Can MERGE replace UPDATE/INSERT duo on a single table?
Question posted by: Spare Brain
(Guest)
on
July 19th, 2005 10:45 PM
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
1
Answer Posted
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')
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,033 network members.
Top Community Contributors
|
