Merge into学习笔记

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);

如果查询结果有多条 

Merge碰到ORA-30926 无法在源表中获得一组稳定的行

MERGE INTO "tar" "T1" USING(

SELECT * FROM (
SELECT t.*,row_number()over(partition by "me_target_unique" ORDER BY 1) rn FROM "bd" t WHERE "me_target_unique" IS NOT NULL)
WHERE
rn=1

) "T2"
ON
("T1"."me_target"="T2"."me_target_unique")
-- WHEN MATCHED THEN
-- UPDATE SET "T1"."me_drugname"="T2"."me_target_unique"
WHEN NOT MATCHED THEN
INSERT ("T1"."me_target","T1"."me_drugname")VALUES("T2"."me_target_unique",'新增')
;