MERGE

[swaroop gowda]

Hi,

 

I would like to know is it possible to DELETE rows from target table before INSERT statement in MERGE statement?

 

Eg:

MERGE INTO TR tr USING SR sr ON (tr.col1 = sr.col1)

WHEN MATCHED THEN

UPDATE SET tr.col2 = sr.col2 etc....

 

WHEN NOT MATCHED THEN

 

DELETE WHERE tr.col1 = sr.col1;

 

INSERT (tr.col1,trcol2,tr.col3) VALUES

(sr.col1, sr.col2, sr.col3);

--
Thanks & Regards
Swaroop Thailuru Swamy

[Avinash Naidu]

I could not get it, you are finding a match on tr.col1 = sr.col1, if it matches you are updating, if it does not match you will insert.

 

Now you want to delete before insert and criteria is again delete where tr.col1 = sr.col1, when there are no records matching this criteria , what will it delete?

--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en

[swaroop gowda]

Target table will be having existing data so before inserting I have to delete.

Yeah your right generally there will not be but my case we have several columns in ON condition.

 

Like,

ON (tr.col1 = sr.col2 AND tr.col2 AND sr.col2 and tr.col3 = sr.col3)

....

...

DELETE where tr.col1 = sr.col1 and tr.col2 = sr.col2;

 

This will delete only for the above condition and col3 in matching condition makes it unique in target table.

It is like deleting previous month data before inserting current month data.