Skip to Main Content

Breadcrumb

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.

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 23, 2016 - 9:50 am UTC

Answered by: Connor McDonald - Last updated: June 25, 2016 - 3:02 am UTC

Category: Developer - Version: 12c

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Advanced Replication

You Asked


We have two tables A, B.

in A table
ID is matching columns in bother the tables.

ID REF NO NAME
0434 98 93 ABC
0423 18 61 XYZ
4875 474 45 tyh

In B table

ID RE NO NAME
0434 99 93 ABC
0423 18 61 XYZ
0476 45 56 rrt


We are comparing A table data with b table. for this we have used merge statement.

condition:

When the rows are matched we are updated in A table (that is 0434, 0423 id's) and if any rows are not present in the a it should insert into A (that is 0476 id). and also if any rows not matched we need to delete those records in A table (4875 should be delete from A tables).


Please help me.


We have used merged statement for this below.

MERGE INTO A
USING (select * from b)
ON (a.ID = b.ID)
WHEN MATCHED THEN
UPDATE SET A.RE=B.RE,
A.NO=B.NO

WHEN NOT MATCHED THEN

INSERT (ID,REF,NO,NAME) values( B.ID,B.REF,B.NO,B.NAME)

1)How to delete not matched rows by using merge statement.






and we said...

MERGE does not support a DELETE option on the when-not-matched clause, only on the what-matched clause.

Options you could consider

1) after your merge, run

delete from A
where id not in ( select id from B )

2) if the merge is always a full table-to-table merge of all data, you could do have the merge set an additional column (eg merged='Y'), and then do

delete from A where merged is null


and you rated our response

  (2 ratings)

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

Reviews

June 24, 2016 - 6:27 am UTC

Reviewer: A reader

Hi Tom,

One question here

we are doing delete with not in , will allow, if we get more than 1000 values from b table.
delete from A
where id not in ( select id from B )

second point i did not understand , could you give an example for this.
Connor McDonald

Followup  

June 25, 2016 - 3:02 am UTC

There is no problem with the number of elements

delete from T where x not in ( select col from T1 )

is *not* the same as

delete from T where x not in ( 1,2,3,4,5, .... )

Compare, then sync

June 24, 2016 - 7:00 am UTC

Reviewer: Stew Ashton from Paris, France

If I understand correctly, you want to synchronize A to B. In other words, A should become identical to B.

To do that, I suggest using Tom Kyte's "compare tables" method to find what needs to be inserted, updated or deleted, then merge to A using the result. For example:
drop table a purge;
create table a(
ID, REF, NO, NAME) as select 
0434, 98, 93, 'ABC' from dual union all select 
0423, 18, 61, 'XYZ' from dual union all select 
4875, 474, 45, 'tyh' from dual;

drop table b purge;
create table b(
ID, REF, NO, NAME) as select 
0434, 99, 93, 'ABC' from dual union all select 
0423, 18, 61, 'XYZ' from dual union all select 
0476, 45, 56, 'rrt' from dual;
  
merge into A O
using (
  select * from (
    select
    "ID", "REF", "NO", "NAME",
    COUNT(*) over(partition by ID)
    - SUM(Z##_CNT) Z##IUD_FLAG
    from (
      select
      "ID", "REF", "NO", "NAME",
      -1 Z##_CNT
      from A O
      union all
      select
      "ID", "REF", "NO", "NAME",
      1 Z##_CNT
      from B N
    )
    group by
    "ID", "REF", "NO", "NAME"
    having SUM(Z##_CNT) != 0
  )
  where Z##IUD_FLAG < 3
) N
on (
  O.ID=N.ID
)
when matched then update set
  "NAME"=N."NAME", "NO"=N."NO", "REF"=N."REF"
  delete where N.Z##IUD_FLAG = 2
when not matched then insert (
  "ID", "REF", "NO", "NAME"
) values(
  N."ID", N."REF", N."NO", N."NAME"
);
The advantage of this method is that it does minimal changes to the target table. If you run this statement twice, the second time you will get "0 rows merged".

This statement was generated from the COMPARE_SYNC package I wrote. You can find it here: https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/