--MERGE INTO 的基本写法
MERGE INTO sc_test target_table --主表,即需要被修改的表
USING (
SELECT
id,
(ROW_NUMBER ( ) OVER ( PARTITION BY org ORDER BY sort ASC ) ) AS new_sort
FROM
sc_test
WHERE
org IN ( SELECT org FROM sc_test HAVING COUNT( sort ) > 1 GROUP BY sort, org )
) source_table --从表,即来源表
ON ( target_table.id = source_table.id ) --连接条件
WHEN MATCHED --在匹配的记录中进行 交集则更新
THEN UPDATE SET --更改主表信息
target_table.sort = source_table.new_sort
WHEN NOT MATCHED BY TARGET --源表中有,目标表中无则新增
THEN INSERT (id, sort, org)
VALUES (source_table .category_id, source_table .category_name, source_table.amount)
WHEN NOT MATCHED BY SOURCE --表名中无,目标表中有则删除
THEN DELETE;
05-05
1870