Reputation: 208
I want to Update or insert Oracle Table based on condition.
Consider that in the table have 2 columns (like id and name), one or more name having the same id. In this situation i want to check the id and name (like 1,'Buyer'), if its exist then i want to update name 'Buyer' to 'Service Provider'. otherwise i want to just insert the values (1,'Service Provider').
I tried this through Merge, but it's update all the name column of id 1 to 'Service Provider'.
merge into party_type p
using (select 1 party_id, 'Buyer' party_type from dual) t
on (t.party_id = p.party_id)
when matched then
update set party_type = 'Service Provider'
when not matched then
insert (party_id,party_type) values(1,'Service Provider');
Available data in the table:
1 Buyer
1 Buyer Agent
1 Vendor
Thanks in advance.
Upvotes: 1
Views: 412
Reputation: 2115
you need to join on both columns
merge into party_type p
using (select 1 party_id, 'Buyer' party_type from dual) t
on (t.party_id = p.party_id and t.party_type = p.party_type)
when matched then
update set party_type = 'Service Provider'
when not matched then
insert (party_id,party_type) values(1,'Service Provider');
Upvotes: 1