OraQA

The following SQL pattern can be used to replace the data of the “Target” table with the data of the “Source” table in a SQL Statement.

CREATE TABLE target AS
SELECT 1 AS num, 'will_be_updated' AS ch FROM DUAL
UNION ALL
SELECT 2 AS num, 'will_be_updated' AS ch FROM DUAL
UNION ALL
SELECT 20 AS num,'will_be_deleted' AS ch FROM DUAL;

CREATE TABLE source AS
SELECT LEVEL num, CHR(ASCII('A')+LEVEL-1) ch
FROM DUAL CONNECT BY LEVEL <= 5;

SELECT * FROM target;

       NUM CH
---------- ---------------
         1 will_be_updated
         2 will_be_updated
        20 will_be_deleted                                              

SELECT * FROM source;

       NUM C
---------- -
         1 A
         2 B
         3 C
         4 D
         5 E      

MERGE INTO target tgt
   USING (SELECT src.num,
                 src.ch,
                 tgt.num AS tgt_num,
                 tgt.ch AS tg_ch
            FROM SOURCE src
                 FULL OUTER JOIN target tgt ON (src.num = tgt.num)
                 ) src
   ON (src.tgt_num = tgt.num)
   WHEN MATCHED THEN
      UPDATE
         SET tgt.ch = src.ch
         WHERE NVL (tgt.ch, CHR (0)) != NVL (src.ch, CHR (0))
      DELETE
         WHERE src.num IS NULL
   WHEN NOT MATCHED THEN
      INSERT (tgt.num, tgt.ch)
      VALUES (src.num, src.ch);

The "Target" table is identical to the "Source" table after executing the "Merge" SQL statement.

SELECT * FROM target;

       NUM CH
---------- ---------------
         1 A
         2 B
         3 C
         4 D
         5 E

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.