Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: additional condition of update in merge
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 toolReceived on Thu Mar 10 2005 - 14:34:47 CST