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

"Spare Brain" <spare_brain_at_yahoo.com> wrote in message news:c4v2qs$3lm2_at_kcweb01.netnews.att.com...
> Hi Folks,
>
> I need to INSERT data into the table where the row may already be present.
> Can MERGE help me out? I'm limited to using SQL only, and thew DB is Oracle
> 9.2.
>
> The low-tech solution would be to issue a SELECT and do an update if the row
> is present, and an INSERT if the row is absent. I thought MERGE can help you
> out - but I'm not able to do it. here's the merge statement I tried, which
> seems to affect zero rows!
>
> merge into employee s
> using
> (select * from employee where user_id = 'john123') 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
> ('john123', 50000, 'foo', 'ALL')
> /
>
> Thanks
> SB
>
>

You need to read the syntax doco for merge a little more carefully.

Its goes like this:

merge into <target_table>
using <table>|<query>|<view>
on (<join condition>)
when matched then <update clause>
when not matched then <insert clause>
/

In the above, the using <query> should specify the source of data. In your case, the source of data is coming out to no rows (since the row does not exist).

Consider the following (The last query is what I'd suggest you should follow) .. YMMV:

SQL> create table employee (user_id varchar2(40),pay number, service_name varchar2(40), authorized_for varchar2(3)   2 )
  3 /

Table created.

SQL> insert into employee values ('av',1000000,'foo','ALL');

1 row created.

SQL> commit;

Commit complete.

• Your clause SQL> merge into employee s 2 using 3 (select * from employee where user_id = 'john123') st 4 ON (s.user_id = st.user_id) 5 when matched then 6 update set s.pay=50000 7 when not matched then 8 insert (s.user_id, s.pay, s.service_name, s.authorized_for) values 9 ('john123', 50000, 'foo', 'ALL') 10 /

0 rows merged.

• 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.

SQL> select * from employee;

USER_ID                                         PAY
---------------------------------------- ----------
SERVICE_NAME                             AUT
---------------------------------------- ---
av                                          1000000
foo                                      ALL

john123                                       50000
foo                                      ALL

SQL> commit;

Commit complete.

• Another unintended clause SQL> merge into employee s 2 using 3 (select * from employee) st 4 ON (s.user_id = st.user_id and st.user_id = 'john123') 5 when matched then 6 update set s.pay=1000 7 when not matched then 8 insert (s.user_id, s.pay, s.service_name, s.authorized_for) values 9 ('john123', 50000, 'foo', 'NA') 10 /

2 rows merged.

SQL> set lines 120
SQL> select * from employee;

USER_ID                                         PAY SERVICE_NAME                             AUT
---------------------------------------- ---------- ---------------------------------------- ---
av                                          1000000 foo                                      ALL
john123                                        1000 foo                                      ALL
john123                                       50000 foo                                      NA

SQL> rollback;

Rollback complete.

SQL> select * from employee;

USER_ID                                         PAY SERVICE_NAME                             AUT
---------------------------------------- ---------- ---------------------------------------- ---
av                                          1000000 foo                                      ALL
john123                                       50000 foo                                      ALL

SQL> merge into employee s
  2 using
  3 (select 'john123' user_id, 1000 pay, 'foo' service_name, 'ALL' authorized_for from dual) st   4 ON (s.user_id = st.user_id)
  5 when matched then
  6 update set s.pay=10
  7 when not matched then
  8 insert (s.user_id, s.pay, s.service_name, s.authorized_for) values   9 (st.user_id, st.pay, st.service_name, st.authorized_for)  10 /

1 row merged.

SQL> select * from employee;

USER_ID                                         PAY SERVICE_NAME                             AUT
---------------------------------------- ---------- ---------------------------------------- ---
av                                          1000000 foo                                      ALL
john123                                          10 foo                                      ALL

HTH Anurag Received on Tue Apr 06 2004 - 16:53:12 CDT