merge into语句是用来合并两张表的数据的,
比如我们想把一张表的数据批量更新到另外一张表,就可以用merge into语句。
具体有哪些业务场景呢?
1.数据同步
2.数据转换
3.基于源表对目标表进行增,删,改的操作。
MERGE的语法
MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN
operation
WHEN NOT MATCHED THEN
operation;
注意:其中最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句。
例如:
MERGE INTO tmp01 t0
using(
SELECT DISTINCT t1.FPRODUCTID
FROM TMP01 t0
INNER JOIN (SELECT DISTINCT data.FPRODUCTID
FROM T_CB_PRORESTORECALDATA data
INNER JOIN T_HS_OUTACCTG acctg ON (acctg.FID = data.FACCTGID AND (data.FMATERIALID > 0))
WHERE (acctg.FDIMENSIONID = 1 AND (acctg.FYEARPERIOD < 202308))) t1
ON t1.FPRODUCTID = t0.FMATERIALID
LEFT OUTER JOIN TMP02 cmpQty ON t0.FID = cmpQty.FID
WHERE ISNULL(cmpQty.FID, 0) = 0) t1
ON (t1.FID = t0.FMATERIALID)
WHEN MATCHED THEN
UPDATE SET t0.FNeedRestoreFromCalData = 1;