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

From: Spare Brain <spare_brain_at_yahoo.com>
Date: Tue, 6 Apr 2004 16:18:30 -0400
Message-ID: <c4v2qs$3lm2@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 Received on Tue Apr 06 2004 - 15:18:30 CDT