Monday, February 28, 2011

I hate it when I am wrong

I recently learned that one of my blog posts about the Oracle MERGE statement has one not one-hundred percent correct statement.

"This means the DELETE does not use column data in the USING clause sub-query; very important. Some column in the UPDATE must be updated for the DELETE to work. To handle this set one or more columns to values that would never appear (in combination) in the data and use those values as a delete trigger."

From Oracle 10g 2 and higher SQL Guide: "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. That is, the DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion."

To clarify:

The DELETE WHERE condition can reference a column from the USING (the source) but the row table in the MERGE INTO (the target) must be have a column updated.

I need to do more experimentation before I correct previous posts and the best way to code for this. I am guessing that a "transaction" column can be in the source (USING) and then that can be tested on the DELETE WHERE and is part of a DECODE(u.indicator,'DELETE','i.column,u.column) so false data are not needed for columns with a NOT NULL constraint.

No comments: