Skip to Main Content
  • Questions

  • How to get table in sync using merge query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bhushan.

Asked: October 05, 2019 - 5:16 am UTC

Last updated: October 08, 2019 - 9:48 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello Tom,
I have 2 tables Source and Target, I want Target table to be in sync with Source as and when query runs.

Following is the columns structure of both of the tables. I want all columns except (Contact_no) have to be in sync in Target table.

CREATE TABLE T_SOURCE (ID NUMBER(5) , Part_no VARCHAR2(10), Country_Code VARCHAR2(2), Contact_no NUMBER(10));

INSERT INTO T_SOURCE VALUES (1, 'AB123', 'IN',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (3, 'RT123', 'CH',2512121212);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);

Source Table
================================================
Unique_ID Part_no    Country_Code   Contact_no
1         AB123      IN             2512121212
2         AD345      US             2518571122
3         RT123      CH             2512121212
4         UY123      EN             2587452548


CREATE TABLE T_TARGET (ID NUMBER(5) , Part_no VARCHAR2(10), Country_Code VARCHAR2(2), Action CHAR(1));


Initially i will copy 3 columns data into Target table with Action = "I". 

INSERT INTO T_TARGET SELECT ID,Part_no,Country_Code,'I' FROM T_SOURCE;

Target Table
===============================================
Unique_ID Part_no  Country_Code Action
1         AB123    IN           I
2         AD345    US           I 
3         RT123    CH           I
4         UY123    EN           I


Now as and when SQL runs it will update, insert or delete Target table as per Source table with Action flag.

Next Day Source Table changed
================================================
Unique_ID Part_no  Country_Code    Contact_no
1         AB123    US              2512121212  -- Updated Country_Code as "US"
2         AD345    US              2518571122  -- No Change
3         RT123    CH              2512121212  -- Deleted from Source
4         UY123    EN              2587452548  -- No Change
5         APD25    DL              1111111111  -- New Inserted

Target Table
===============================================
Unique_ID Part_no  Country_Code Action
1  AB123           US    U
2  AD345           US    I 
3  RT123           CH    D -- Deleted from Source
4  UY123           EN    I
5  APD25           DL    I     -- Newly Inserted


I have written below Merge statement but its not working properly, after initial insert with Action flag "I" when i execute, it is updating all the records with action flag as "U"

MERGE INTO Target d2
USING (SELECT Unique_id, Part_no, Country_code 
       FROM Source
       UNION ALL
       SELECT a.Unique_id, a.Part_no, a.Country_code
       FROM Target a LEFT JOIN Source b ON a.Unique_id=b.Unique_id
       WHERE b.Unique_id IS NULL 
       ) d
ON (d2.Unique_id=d.Unique_id)
WHEN NOT MATCHED THEN
   INSERT(Unique_id, Part_no, Country_code, Action)
   VALUES(d.Unique_id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET d2.Action = 'U', d2.Country_code = d.Country_code;
--   DELETE WHERE d2.loc='DELETE ME';


Kindly help me on this.

and Chris said...

If you run merge twice with the same source data, the first pass will load any missing rows. And on the second execution all the rows in the source are in the target. So you'll update all the rows!

To avoid this, remove rows from the source already in the target with MINUS or NOT EXISTS.

While you're at it, to identify the deleted rows change the outer join to return columns from the source.

So your source query would be something along the lines of:

(
SELECT id, Part_no, Country_code 
FROM   T_SOURCE
MINUS
SELECT id, Part_no, Country_code 
FROM   T_TARGET
)
UNION ALL
SELECT a.id, b.Part_no, b.Country_code
FROM T_TARGET a 
LEFT JOIN T_SOURCE b 
ON a.id=b.id
WHERE b.id IS NULL


Then in the MATCHED clause you can check if source.part_no is null to delete or flag it as needed:

CREATE TABLE T_SOURCE (
  ID NUMBER(5) , Part_no VARCHAR2(10), 
  Country_Code VARCHAR2(2), Contact_no NUMBER(10)
);

CREATE TABLE T_TARGET (
  ID NUMBER(5) , Part_no VARCHAR2(10), 
  Country_Code VARCHAR2(2), Action CHAR(1)
);

INSERT INTO T_SOURCE VALUES (1, 'AB123', 'IN',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (3, 'RT123', 'CH',2512121212);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);

INSERT INTO T_TARGET SELECT ID,Part_no,Country_Code,'I' FROM T_SOURCE;
commit;

delete t_source;
INSERT INTO T_SOURCE VALUES (1, 'AB123', 'US',2512121212);
INSERT INTO T_SOURCE VALUES (2, 'AD345', 'US',2518571122);
INSERT INTO T_SOURCE VALUES (4, 'UY123', 'EN',2587452548);
INSERT INTO T_SOURCE VALUES (5, 'UY123', 'EN',1111111111);
commit;

MERGE INTO T_TARGET d2
USING ((
        SELECT id, Part_no, Country_code 
        FROM   T_SOURCE
        MINUS
        SELECT id, Part_no, Country_code 
        FROM   T_TARGET
        )
       UNION ALL
       SELECT a.id, b.Part_no, b.Country_code
       FROM T_TARGET a 
       LEFT JOIN T_SOURCE b 
       ON a.id=b.id
       WHERE b.id IS NULL 
       ) d
ON (d2.id=d.id)
WHEN NOT MATCHED THEN
   INSERT(id, Part_no, Country_code, Action)
   VALUES(d.id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET d2.Action = 'U', d2.Country_code = d.Country_code
   DELETE WHERE d.part_no is null;

select * from T_TARGET;

ID    PART_NO    COUNTRY_CODE    ACTION   
    1 AB123      US              U         
    2 AD345      US              I         
    4 UY123      EN              I         
    5 UY123      EN              I     

rollback;

MERGE INTO T_TARGET d2
USING ((
        SELECT id, Part_no, Country_code 
        FROM   T_SOURCE
        MINUS
        SELECT id, Part_no, Country_code 
        FROM   T_TARGET
        )
       UNION ALL
       SELECT a.id, b.Part_no, b.Country_code
       FROM T_TARGET a 
       LEFT JOIN T_SOURCE b 
       ON a.id=b.id
       WHERE b.id IS NULL 
       ) d
ON (d2.id=d.id)
WHEN NOT MATCHED THEN
   INSERT(id, Part_no, Country_code, Action)
   VALUES(d.id, d.Part_no, d.Country_code, 'I')
WHEN MATCHED THEN
   UPDATE SET 
     d2.Action = case 
       when d.part_no is not null then 'U' 
       else 'D' 
     end, 
     d2.Country_code = case 
       when d.part_no is not null then d.Country_code 
       else d2.Country_code 
    end;

select * from T_TARGET;

ID    PART_NO    COUNTRY_CODE    ACTION   
    1 AB123      US              U         
    2 AD345      US              I         
    3 RT123      CH              D         
    4 UY123      EN              I         
    5 UY123      EN              I

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

on Full outer joins

Rajeshwaran, Jeyabal, October 07, 2019 - 2:35 pm UTC

Another approach would be to use a FULL outer joins - something like this.
demo@PDB1> select * from t_source;

        ID PART_NO    CO CONTACT_NO
---------- ---------- -- ----------
         1 AB123      US 2512121212
         2 AD345      US 2518571122
         4 UY123      EN 2587452548
         5 UY123      EN 1111111111

demo@PDB1> select * from t_target;

        ID PART_NO    CO A
---------- ---------- -- -
         1 AB123      IN I
         2 AD345      US I
         3 RT123      CH I
         4 UY123      EN I

demo@PDB1> select nvl( s.id, t.id ) id,
  2     nvl(s.part_no,t.part_no) part_no,
  3     nvl(s.Country_Code,t.country_code) Country_Code,
  4     case when s.id is null and t.id is not null then 'D'
  5              when s.id is not null and t.id is null then 'I'
  6              when s.id = t.id and (
  7                     s.part_no <> t.part_no or
  8                     s.country_code <> t.country_code ) then 'U'
  9          else t.action end action_flag
 10  from t_source s
 11     full outer join
 12     t_target t
 13  on ( s.id = t.id ) ;

        ID PART_NO    CO A
---------- ---------- -- -
         1 AB123      US U
         2 AD345      US I
         3 RT123      CH D
         4 UY123      EN I
         5 UY123      EN I

demo@PDB1> merge into t_target t1 using (
  2             select nvl( s.id, t.id ) id,
  3                     nvl(s.part_no,t.part_no) part_no,
  4                     nvl(s.Country_Code,t.country_code) Country_Code,
  5                     case when s.id is null and t.id is not null then 'D'
  6                              when s.id is not null and t.id is null then 'I'
  7                              when s.id = t.id and (
  8                                     s.part_no <> t.part_no or
  9                                     s.country_code <> t.country_code ) then '
 10                             else t.action
 11                             end action_flag
 12             from t_source s
 13                     full outer join
 14                     t_target t
 15             on ( s.id = t.id )      ) t2
 16  on ( t1.id = t2.id )
 17  when matched then
 18     update set
 19             t1.part_no = t2.part_no,
 20             t1.country_code = t2.country_code,
 21             t1.action = t2.action_flag
 22  when not matched then
 23     insert ( t1.id,t1.part_no,t1.country_code,t1.action ) values(
 24             t2.id, t2.part_no,t2.country_code,t2.action_flag )
 25  /

5 rows merged.

demo@PDB1> select * from t_target;

        ID PART_NO    CO A
---------- ---------- -- -
         1 AB123      US U
         2 AD345      US I
         3 RT123      CH D
         4 UY123      EN I
         5 UY123      EN I

demo@PDB1>

Chris Saxon
October 08, 2019 - 9:48 am UTC

Yep, that works too.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.