合并插入更新删除
MERGE INTO targetTable
USING sourceTable
ON (targetTable.PKID = sourceTable.PKID)
WHEN MATCHED AND (targetTable.PKID > 100) THEN
DELETE
WHEN MATCHED AND (targetTable.PKID <= 100) THEN
UPDATE SET
targetTable.ColumnA = sourceTable.ColumnA,
targetTable.ColumnB = sourceTable.ColumnB
WHEN NOT MATCHED THEN
INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);
WHEN NOT MATCHED BY SOURCE THEN
DELETE
; --< Required
描述:
-
MERGE INTO targetTable
- 要修改的表 -
USING sourceTable
- 数据源(可以是表或视图或表值函数) -
ON ...
-targetTable
和sourceTable
之间的连接条件。 -
WHEN MATCHED
- 找到匹配项时要采取的措施 -
AND (targetTable.PKID > 100)
- 为采取行动必须满足的附加条件
-
THEN DELETE
- 删除targetTable
的匹配记录 -
THEN UPDATE
- 更新SET ....
指定的匹配记录列 -
WHEN NOT MATCHED
- 在targetTable
中找不到匹配时要采取的行动 **** -
WHEN NOT MATCHED BY SOURCE
- 在sourceTable
中找不到匹配时要采取的行动 ****
评论:
如果不需要特定的操作,则省略条件,例如删除 WHEN NOT MATCHED THEN INSERT
将阻止插入记录
合并语句需要终止分号。
限制:
WHEN MATCHED
不允许采取行动UPDATE
动作只能更新一行。这意味着连接条件必须产生唯一匹配。