merge into tb1 a
using (select b.id, c.price from tb2 b left join tb3 c where b.id = c.id) d --这里using(可以子查询)
on a.id = d.id
when matched then
udpdate a.price = b.price where d.product_name like '%ct' --update可以加过滤条件
when not matched then
insert (a.price) values (b.price) where d.product_name like '%ct' --insert对象也可以加过滤条件,已ct结尾的
merge into products p using(select * from newproducts) np
on (p.product_id=np.product_id)
when matched then
update set p.product_name=np.product_name
when not matched then
delete where p.product_id=np.product_id where np.product like '%c'
--清除行的前提是要找到满足p.product_id=np.product_id的记录,若t2.name='a' 不满足t1.name=t2.name,delete不生效!
--delete子句的where必须在最后面
merge into t2
using(select count(*) cnt from t2 where name='d') t --注意这里的伪表t一定要存在
on t2.name=t.name
when matched then
update set t2.money=100
when not matched then
insert
values('d',200) --对t2表进行自我更新,若在t2表中发现name='d'的记录,就将其
字段更新为100,若记录不存在,自动增加name='d'的记录
using (select b.id, c.price from tb2 b left join tb3 c where b.id = c.id) d --这里using(可以子查询)
on a.id = d.id
when matched then
udpdate a.price = b.price where d.product_name like '%ct' --update可以加过滤条件
when not matched then
insert (a.price) values (b.price) where d.product_name like '%ct' --insert对象也可以加过滤条件,已ct结尾的
merge into products p using(select * from newproducts) np
on (p.product_id=np.product_id)
when matched then
update set p.product_name=np.product_name
when not matched then
delete where p.product_id=np.product_id where np.product like '%c'
--清除行的前提是要找到满足p.product_id=np.product_id的记录,若t2.name='a' 不满足t1.name=t2.name,delete不生效!
--delete子句的where必须在最后面
merge into t2
using(select count(*) cnt from t2 where name='d') t --注意这里的伪表t一定要存在
on t2.name=t.name
when matched then
update set t2.money=100
when not matched then
insert
values('d',200) --对t2表进行自我更新,若在t2表中发现name='d'的记录,就将其
字段更新为100,若记录不存在,自动增加name='d'的记录