oracle merge
--DROP TABLE PRODUCTS;
--DROP TABLE NEWPRODUCTS;
create table test_meger_PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into test_meger_PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into test_meger_PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into test_meger_PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into test_meger_PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into test_meger_PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table test_meger_NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into test_meger_NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into test_meger_NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into test_meger_NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into test_meger_NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
=================================================================
select * from test_meger_products;
select * from test_meger_newproducts;
=================================================================
merge into test_meger_products a using test_meger_newproducts b on (a.PRODUCT_ID = b.PRODUCT_ID )
when matched then
update set a.product_name = b.product_name,a.category=b.category;
=================================================================
merge into test_meger_products a
using test_meger_newproducts b
on (a.PRODUCT_ID = b.PRODUCT_ID)
when not matched then
INSERT
(PRODUCT_ID
,PRODUCT_NAME
,CATEGORY)
VALUES
(b.PRODUCT_ID
,b.PRODUCT_NAME
,b.CATEGORY);
select * from test_meger_products;
select * from test_meger_newproducts;
=================================================================
merge into test_meger_products a
using (select count(*) co
from test_meger_products b
where b.PRODUCT_ID = 1501) c
on (c.co <> 0 )
when matched then
update set a.product_name='update_1501' where a.product_id = '1501'
when not matched then
insert (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES ('1501' ,'insert-1' ,'insert-2');
=================================================================
merge into test_meger_products a
using (select '1501' product_id, count(*) co
from test_meger_products b
where b.PRODUCT_ID = 1501) c
on (a.product_id = c.product_id )
when matched then
update set a.product_name='update_15045'
when not matched then
insert (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES ('1501' ,'insert-1' ,'insert-2');
=================================================================
