SQL> select count(1) from ps_pay_check;

 
  COUNT(1)

----------

    638064

SQL> UPDATE ps_pay_check a

  2     SET (a.name, a.paycheck_name) =

  3            (SELECT b.new_name, b.new_name paycheck_name

  4               FROM hx_names_stg b

  5              WHERE b.old_name = a.name

  6                AND b.old_name = a.paycheck_name

  7                AND ROWNUM < 2)

  8   WHERE EXISTS ( SELECT NULL

  9                    FROM hx_names_stg b

 10                   WHERE b.old_name = a.name

 11                     AND b.old_name = a.paycheck_name

 12                     AND ROWNUM < 2)

 13  /

 
473273 rows updated.
Elapsed: 00:18:18.04

Execution Plan

----------------------------------------------------------

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=2131 Card=31904 Byte

          s=1020928)

   1    0   UPDATE OF 'PS_PAY_CHECK'
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'PS_PAY_CHECK' (Cost=2131 Card=
          31904 Bytes=1020928)

   4    2       COUNT (STOPKEY)
   5    4         FILTER
   6    5           INDEX (RANGE SCAN) OF 'HX_NAMES_STG_IDX_003' (NON-

          UNIQUE) (Cost=1 Card=2 Bytes=32)

   7    0   COUNT (STOPKEY)
   8    7     FILTER
   9    8       TABLE ACCESS (BY INDEX ROWID) OF 'HX_NAMES_STG' (Cost=

          3 Card=2 Bytes=62)

  10    9         INDEX (RANGE SCAN) OF 'HX_NAMES_STG_IDX_003' (NON-UN

          IQUE) (Cost=1 Card=2)
 

Statistics

----------------------------------------------------------

       2904  recursive calls
    3502430  db block gets
     639294  consistent gets
      30572  physical reads
  489720600  redo size
        865  bytes sent via SQL*Net to client
       1023  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     473273  rows processed

MERGE INTO ps_pay_check p
USING (
    SELECT a.rowid AS rid, b.new_name
    FROM ps_pay_check a
    JOIN (
        SELECT old_name, MAX(new_name) AS new_name
        FROM hx_names_stg
        WHERE old_name = paycheck_name
        GROUP BY old_name
    ) b ON (b.old_name = a.name)
    WHERE a.name = a.paycheck_name
) s
ON p.rowid = s.rid
WHEN MATCHED THEN UPDATE
SET name = s.new_name
,   paycheck_name = s.new_name