Re: Merge command - Delete - Part Two...
Date: Sat, 13 Jun 2009 21:14:11 +0200
Message-Id: <669862713_at_web.de>
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-lReceived on Sat Jun 13 2009 - 14:14:11 CDT