Re: additional condition of update in merge

cschang (cschang_at_maxinter.net) wrote:
: Malcolm Dew-Jones wrote:

: > Andy Hassall (andy_at_andyh.co.uk) wrote:
: > : On Wed, 09 Mar 2005 21:32:57 -0500, cschang <cschang_at_maxinter.net> wrote:
: >
: > : >I tried to learn the merge, so i tried this in a procedure however, it
: > : >gave me an error
: > : > MERGE INTO ORDER_STATUS_NOTIFICATION o1
: > : >USING ( SELECT COUNT(*) v_count
: > : > FROM ORDER_STATUS_NOTIFICATION
: > : > WHERE CHECKOUT_TXN_ID = strCheckoutTxnID ) o2
: > : > ON ( o2.v_count > 0)
: > : > WHEN MATCHED THEN
: > : >UPDATE
: > : > SET
: > : > EVENT_TYPE = 'FAILED',
: > : > DATE_OF_EVENT = SYSDATE
: > : >
: > : > WHERE EVENT_TYPE <> 'FAILED' <-- Err: ORA-00905, Missing Keyword
: >
: > : Merge doesn't allow a WHERE clause here.
: >
: > : http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm
: >
: > : merge::=
: > : MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
: > : { table | view | subquery } [t_alias] ON ( condition )
: > : WHEN MATCHED THEN merge_update_clause
: > : WHEN NOT MATCHED THEN merge_insert_clause;
: >
: > : merge_update_clause::=
: > : UPDATE SET column = { expr | DEFAULT } [, column = { expr | DEFAULT }]...
: >
: > : There's no room in there for a WHERE clause.
: >
: > : >Does that mean I can not add additional where condition for the update
: > : >section? I was running this on 9.2 version.
: >
: > : Yep, that's exactly what it means.
: >
: > Which makes a lot of sense if you think about it. A merge row is either
: > replacing an existing row or adding a new row, and the point of the update
: > is to provide the details on how to do the replacement when the merge row
: > matches. I.e. the merge itself acts as the WHERE clause for the update
: > because that is what selects the rows to be updated.
: >
: > However, perhaps the SET clause can use functions such as DECODE to "fine
: > tune" how the update of each row works.
: >
: > set DATE_OF_EVENT =
: > decode( EVENT_TYPE , 'FAILED', DATE_OF_EVENT , sysdate )
: >
: > or such like.
: >
: > --
: >
: > This space not for rent.
: If i remember correctly, using the DECODE is different to additional
: condition in where. With DECODE, you still update the rest of the
: columns of the row with new values, with where
: condition, that row will not be touched.

With the where condition none of rows will be touched - since the where condition is not allowed in the first place.

I was suggesting you use decode to selectively update any column to its _original_ value, which would have the same effect as not updating the column at all. The only question is how to get the original value during the update. I have never had to do that, so I have never figured out if and/or how to do it - as I said, "_perhaps_" it is possible.

Accessing old and new values is common in other situations (I am thinking of triggers) and is handled by a convention involving some special bind variables provided for the purpose. I would check the docs to see if anything similar is available when using the merge statement.

There appear to be loads of interesting examples online showing how to use merge, so perhaps an example shows how to do something like this.

--

This space not for rent.