Re: Merge command - Delete - Part Two...

Robert,

I don't think this is a bug, but a "feature" and seems to behave as documented.

As documented here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081030

"Specify the DELETE where_clause to clean up data in a table while populating 
or updating it. The only rows affected by this clause are those rows in the 
destination table that are updated by the merge operation."

Your example does not perform the DELETE operation because of the WHERE-clause 
specified for the UPDATE.

You can only delete rows with this clause when they get updated, but your 
"where status = 'INVALID'" predicate of the UPDATE prevents this, and therefore 
the subsequent delete is not evaluated for this row. Removing this 
"where"-clause from the UPDATE part will have the MERGE command remove the row.

Although I don't think that this answers your original question.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

> Follow-up question.... Has anyone had a problem with using the merge command 
> and the delete option. I've been working on a real simple test case and this 
> is 
> looking like a bug.... feedback? This is running in 11.1.0.7. Is there 
> something wrong in my merge command or am I just missing some key bit of 
> understanding about merge and the delete option somewhere?

___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und 
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/

--
http://www.freelists.org/webpage/oracle-l

Other related posts: