Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Can MERGE replace UPDATE/INSERT duo on a single table?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US