-- 更新+插入
-- merge into x using() on() when matched then update set when not matched then insert ;
merge into A_MERGE A USING (select B.id,B.name,B.year,B.city from B_MERGE B) C
ON(A.id=C.id)
when matched then
update SET A.name=C.name where C.city != '西安'
when not matched then
insert(A.id,A.name,A.year) values(c.id,C.name,C.year) where C.city='西安';
-- 删除
-- merge into x using() on() when matched then update set delete where when not matched then insert ;
merge into MCD_CUSTGROUP_PROTECT_RECORDS t1
using (select PRODUCT_NO, PROTECT_ID, CREATE_USERID
from MCD_CUSTGROUP_PROTECT_RECORDS_TMP
WHERE PROTECT_ID = 2021041609480293) t2
on (t1.PROTECT_ID = t2.PROTECT_ID AND t1.PRODUCT_NO = t2.PRODUCT_NO)
when matched then
update
set t1.CREATE_USERID = t2.CREATE_USERID delete where t1.PRODUCT_NO = t2.PRODUCT_NO;-- delete条件必须在上面的using on里有/update set 语句不能少
