By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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_for) values
('john123', 50000, 'foo', 'ALL')
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.