How to Fetch merged rows back ! [message #679387] |
Thu, 27 February 2020 03:49 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
I have a requirement, to return the rows which are merged.
Could any one tell me, is there any option or workaround in oracle to get the merged rows?
|
|
|
|
Re: How to Fetch merged rows back ! [message #679390 is a reply to message #679388] |
Thu, 27 February 2020 05:21 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
For Example, I have two tables catelog1 & catelog2
create table catalog1 (id number(3), item varchar2 (20), price number(6))
insert into catalog1 values(1, 'laptop', 800)
insert into catalog1 values(2, 'iphone', 500)
insert into catalog1 values(3, 'camera', 700)
create table catalog2 (id number(3), item varchar2 (20), price number(6))
insert into catalog2 values(1, 'laptop', 899)
insert into catalog2 values(2, 'iphone', 599)
insert into catalog2 values(5, 'video camera', 799)
CATELOG1
ID ITEM PRICE
1 laptop 800
2 iphone 500
3 camera 700
CATELOG2
ID ITEM PRICE
1 laptop 899
2 iphone 599
5 video camera 799
I am merging Catelog2 with Catelog1.
MERGE INTO catalog1 s1 USING catalog2 s2 ON (s1.id = s2.id)
WHEN MATCHED THEN UPDATE SET s1.price = s2.price
WHEN NOT MATCHED THEN INSERT (id, item, price) values (s2.id, s2.item, s2.price)
I want to return the rows which are merged.For example below records i want to fetch as it was updated/inserted.
ID ITEM PRICE
1 laptop 899
2 iphone 599
5 video camera 799
|
|
|