//测试表以及数据
//product_types:产品类型表
select * from product_types;
????? PRODUCT_TYPE_ID NAME
--------------------- ----------
??????????????????? 1 Book
??????????????????? 2 Video
??????????????????? 3 DVD
??????????????????? 4 CD
??????????????????? 5 Magazine
//products:产品信息表
select * from products;
????? PRODUCT_ID????????? PRODUCT_TYPE_ID NAME??????????? DESCRIPTION?????????????????????? PRICE
---------------- ------------------------ --------------- ------------------------------- -------
?????????????? 2??????????????????????? 1 Chemistry?????? Introduction to Chemistry???????? 30.00
?????????????? 3??????????????????????? 2 Supernova?????? A star explodes?????????????????? 25.99
?????????????? 4??????????????????????? 2 Tank War??????? Action movie about a future war?? 13.95
?????????????? 1??????????????????????? 1 Modern Science? A description of modern science?? 19.95
//product_changes:产品信息变更表
create table product_changes(
???????????? product_id integer primary key,
???????????? product_type_id integer references product_types(product_type_id),
???????????? name varchar2(50),
???????????? price number(5,2))
//变更数据
select * from product_changes;
?????? PRODUCT_ID?????????? PRODUCT_TYPE_ID NAME?????????????????????????????????? PRICE
----------------- ------------------------- ------------------------------------ -------
??????????????? 1???????????????????????? 1 Modern Science???????????????????????? 40.00
??????????????? 2???????????????????????? 1 New Chemistry????????????????????????? 35.00
??????????????? 3???????????????????????? 1 Supernova????????????????????????????? 25.99
?????????????? 13???????????????????????? 2 Lunar Landing????????????????????????? 15.99
?????????????? 14???????????????????????? 2 Submarine????????????????????????????? 15.99
?????????????? 15???????????????????????? 2 Airplane?????????????????????????????? 15.99
?????????????? 16???????????????????????? 5 Programmer???????????????????????????? 20.00
//我想要做的是，将product_changes表中的数据跟新到products表中；
//1.更新products表中原有的数据，2.如果products表中没有的数据，就添加进去
//下面是具体实现代码:
merge into products p
using product_changes pc
on (p.product_id=pc.product_id)
when matched then
???? update
???? set p.product_type_id=pc.product_type_id,
???????? p.name=pc.name,
???????? p.price=pc.price
when not matched then
???? insert
???? (p.product_id,p.product_type_id,p.name,p.price)
???? values
???? (pc.product_id,pc.product_type_id,pc.name,pc.price);
//操作完成后，查看一下products表中产品信息，
//我们发现，原有的数据部分被更新了，而且新添加了一条原来没有的数据
select product_id,product_type_id,name,price
from products;
????????????? PRODUCT_ID?????????????? PRODUCT_TYPE_ID NAME????????????????? PRICE
------------------------ ----------------------------- ------------------- -------
?????????????????????? 2???????????????????????????? 1 New Chemistry???????? 35.00
?????????????????????? 3???????????????????????????? 1 Supernova???????????? 25.99
?????????????????????? 4???????????????????????????? 2 Tank War????????????? 13.95
?????????????????????? 1???????????????????????????? 1 Modern Science??????? 40.00
????????????????????? 13???????????????????????????? 2 Lunar Landing???????? 15.99
????????????????????? 15???????????????????????????? 2 Airplane????????????? 15.99
????????????????????? 14???????????????????????????? 2 Submarine???????????? 15.99
????????????????????? 16???????????????????????????? 5 Programmer??????????? 20.00
//merge into子句常用于几个关联表更新表，和批量加载数据。