Andrew Fraser DBA

oracle update select merge

If you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below.

Sample data:

DROP TABLE a ;
CREATE TABLE a ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '1' , 'yes' , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '2' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '3' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '4' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '5' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '5' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '7' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '8' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '9' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '0' , NULL , NULL ) ;
DROP TABLE b ;
CREATE TABLE b ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '1' , 'yes' , NULL ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '2' , 'maybe' , 'yes' ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '3' , NULL , NULL ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '4' , NULL , 'maybe' ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( 'ne' , 'maybe' , 'maybe' ) ;
COMMIT ;

Two of the rows in table a – rows 2 and 4 – have different values than their equivalent rows in table b. So ideally I want to update just those 2 rows in table a.

1) Simplest method works ok in terms of getting to the correct end result, but updates the whole of table a, all 10 rows. That doesn’t matter for the end result, because is updating columns that already match to the same value, but is an unnecessary performance overhead.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id ) ;

2) Adding an IN check excludes the rows in a that don’t have a counterpart in b, now down to 4 updates.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id )
WHERE a.id IN ( SELECT b.id FROM b ) ;

EXISTS could be used instead of IN, except it requires slightly more code. The second line would be a longer:

WHERE EXISTS ( SELECT NULL FROM b WHERE a.id = b.id ) ;

EXISTS and IN are functionally the same and perform the same; unlike NOT EXISTS and NOT IN which handle NULLs differently.

2b) This MERGE statement is identical, 4 rows merged:

MERGE INTO a USING ( SELECT * FROM b ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2 ;

Merge can do more: INSERT when not matched, delete based on a where clause; so becomes useful when that is needed.

2c) Using an inline view is also identical, 4 rows updated, but this needs a primary or unique constraint to be in place on the table which is not being updated. Without that it will fail with ORA-01779: cannot modify a column which maps to a non key-preserved table.

ALTER TABLE b ADD CONSTRAINT b_pk PRIMARY KEY ( id ) ;
UPDATE (
   SELECT a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
     FROM a JOIN b ON a.id = b.id
   ) v
SET v.a_text1 = v.b_text1 , v.a_text2 = v.b_text2 ;

3) Adding more conditions to the IN clause gets us to the desired point, 2 rows updated the first time it is run, no rows updated on subsequent runs.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id )
WHERE a.id IN (
   SELECT b.id FROM b
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
) ;

The NVLs are needed because ‘something=NULL’ always evaluates to false.

3b) Again, MERGE can do the same thing, 2 rows merged initially, none subsequently:

MERGE INTO a USING ( SELECT * FROM b ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2
   WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
      OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

That works ok for a 1:1 join, but where the second table returns multiple values then this will either toggle the update around the different values each time it runs, orfail altogether with ORA-30926: unable to get a stable set of rows in the source tables depending on the number of multiples. A workaround such as a MAX function or similar will ensure unique values are selected

INSERT INTO b ( id , text1 , text2 ) VALUES ( '4' , 'duplicate' , 'duplicate' ) ;
MERGE INTO a USING ( SELECT b.id , MAX ( b.text1 ) AS text1 , MAX ( b.text2 ) AS text2 FROM b GROUP BY b.id ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2
   WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
      OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

NULL values of id are handled ok without workarounds like this.
3c) And again, using an inline view can do the same thing, 2 rows updated initially, none subsequently.

ALTER TABLE b ADD CONSTRAINT b_pk PRIMARY KEY ( id ) ;
UPDATE (
   SELECT a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
     FROM a JOIN b ON a.id = b.id
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
   ) v
SET v.a_text1 = v.b_text1 , v.a_text2 = v.b_text2 ;

Those statements only tell you the number of rows changed. If you want to know the details of what was changed, and you know for sure that no one else is modifying the data at that time, then you can run a report before the update/merge:

SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
  FROM a JOIN b ON a.id = b.id
 WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
    OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

More likely you need certainty that what you are reporting on is really changed, that means a begin..end block is needed to ensure transaction isolation from any other data modifications at that time. That means writing the report to a log table rather than just selecting the information:

BEGIN
   INSERT INTO log_table
   SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2 , SYSDATE
     FROM a JOIN b ON a.id = b.id
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
   ;
   -- main UPDATE or MERGE statement goes here.
END ;

PL/SQL can be a better alternative, provided is not a large number of updates where performance is needed.

set serverout on
BEGIN
   FOR r1 IN (
      SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
        FROM a JOIN b ON a.id = b.id
       WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
          OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
      )
   LOOP
      dbms_output.put_line ( 'Updating ' || r1.id || ' from ' || r1.a_text1 || ' , ' || r1.a_text2 || ' to ' || r1.b_text1 || ' , ' || r1.b_text2 ) ;
      UPDATE a SET a.text1 = r1.b_text1 , a.text2 = r1.b_text2 WHERE a.id = r1.id ;
   END LOOP ;
END ;