Re: Can MERGE replace UPDATE/INSERT duo on a single table?

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:cFFcc.2557$M3.1802_at_twister.nyroc.rr.com... --snip--
> -- What you actually intended
> SQL> merge into employee s
> 2 using
> 3 (select * from employee) st
> 4 ON (s.user_id = st.user_id and s.user_id = 'john123')
> 5 when matched then
> 6 update set s.pay=50000
> 7 when not matched then
> insert (s.user_id, s.pay, s.service_name, s.authorized_for) values 8
> ('john123', 50000, 'foo', 'ALL') 9
> /
> 10
> 1 row merged.

I did not specify clearly: The above clause is a wrong clause also.

The right clause for your case should probably look something like this:

merge into employee s
using
(select 'john123' user_id from dual) st
ON (s.user_id = st.user_id)
when matched then

    update set s.pay=50000
when not matched then

    insert (s.user_id, s.pay, s.service_name, s.authorized_for) values     (st.user_id, 50000, 'foo', 'ALL')
/

Anurag Received on Tue Apr 06 2004 - 17:05:05 CDT