数据开发之 merge into

merge into会根据条件将源表的数据合并到目标表中。它可以根据匹配的条件决定是更新现有记录、插入新记录，还是删除记录。

语法

MERGE INTO target_table AS target
USING source_table AS source
-- 匹配条件
ON target.key_column = source.key_column 
-- 如果匹配上，则做数据更新，这里可以指定想要更新的字段
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.value1,
               target.column2 = source.value2
-- 如果匹配不上，则做插入
WHEN NOT MATCHED THEN
    INSERT (target.column1, target.column2)
    VALUES (source.value1, source.value2);
;

或者

MERGE INTO target_table AS target
USING(
	select 
		key_column1,key_column2,column1,column2,column3  --...
	from source_table
	where date=${date - 1}
) source

ON target.key_column1 = source.key_column1 AND  target.key_column2 = source.key_column2  -- ...
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.value1,
               target.column2 = source.value2  -- ...

WHEN NOT MATCHED THEN INSERT *
;

---