CODEWITHSUNDEEP
oracle-databaseplsqlmergesql-delete
Magezy
Magezy

Reputation: 109

PLSQL Merge delete

I am trying to merge two tables using the MERGE statement, is there any way of deleting any records in the merged table that have neither been matched or updated?

logic is: if matched leave it. if not matched in source delete. if not matched in target insert.

I know SQL has source/target matching, has oracle anything similar?

Upvotes: 1

Views: 2789

Answers (3)

Justin Cave
Justin Cave

Reputation: 231781

Assuming you are using at least Oracle 10g, there is a DELETE WHERE clause in the MERGE statement.

But, as with A.B. Cade, I'm not sure that I understand what rows you are trying to delete. If you are trying to delete rows from the destination table that don't exist in the source, making the MERGE more of a synchronization than a merge, you could do a DELETE WHERE NOT EXISTS. But if that's the goal, I'd take a step back and look at the problem because it seems likely that there is a better technical approach to whatever business problem you have. If you're trying to synchronize two tables, you're generally either doing replication in which case you ought to be using Oracle's replication technologies like materialized views and Streams or you shouldn't have two copies of the data in the first place and one copy should just be a view or a synonym that references the other.

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16915

I don't really understand: Lets say you have table A and Table B, and lets say you want to merge B to A according to some ID col, do you want that every line in A that has the same ID as B won't be changed and every line in A that doesn't exist in B (according to ID) will be removed but every line in B that doesn't exist in A will be added to A ? If so, doesn't it mean that you want to merge A to B (but change table A)? Why not copy table B to another table C then merge A to C and move data back to A?

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

As far as I know there is no (one shot) SQL for this.

In PL/SQL you can do it, for example, in this (somehow ugly) way:

BEGIN

FOR r in (select a1,a2,b1,b2,b.rowid from a full outer join b on a.a1=b.b1)
loop

  if r.a1 is null 
  then 
     delete from b where rowid=r.rowid;
  end if;

  if r.b1 is null
  then 
     insert into b values (r.a1, r.a2);
  end if;

end loop;
END;

Upvotes: 2

Related Questions