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

[Spare Brain]

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_fo r) values
('john123', 50000, 'foo', 'ALL')
/

Thanks
SB

[Jose Dorado]

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

The problem is that the
select * from employee where user_id = 'john123'

returns no rows.

If it helps this works, if user_id is a unique key

merge into employee s
using
(
select user_id from (
select user_id from employee where user_id = 'john123'
union all
select NULL user_id from dual
) where rownum=1
) 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_fo r) values
('john123', 50000, 'foo', 'ALL')