Re: additional condition of update in merge

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. Basically I have data as

   ID          Flag       ChangeDate     Event
-----      -------      -------------  ---------
    1             Y          2-13-05      A
    1             N          2-14-05      B
    2             Y          2-13-05      A

With Merge I only want to update the column Flag with 'N' and insert new one

So result should be by an update of ID = 1 and insert a new rec with ID = 3

   ID          Flag       ChangeDate     Event
-----      -------      ------------    ---------
   1             Y          2-13-05        A
   1             Y          sysdate        A
   2             Y          2-13-05        A
   3             Y          sysdate         C

or could be

   ID          Flag       ChangeDate     Event
-----      -------      -------------- ---------
   1             Y          2-13-05       A
   1             Y          sysdate       A
   2             Y          2-13-05       A

if then ID = 2 but the Flag is = Y, no update.

C Chang Received on Thu Mar 10 2005 - 20:56:14 CST