Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: additional condition of update in merge

Re: additional condition of update in merge

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 10 Mar 2005 20:34:47 +0000
Message-ID: <0mb131hit4ktierkak0upar4eun4qhqf1k@4ax.com>


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.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Thu Mar 10 2005 - 14:34:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US