关于高斯/Oracle 的 MergeInto 语法

临时表方式拼装 MERGE INTO SQL 语句

1、主键冲突则更新

MERGE INTO table_9_copy T1 USING (
select id,name,age from (
select row_number() over (PARTITION by id order by age desc) row_number_column, id,name,age from (
select '0' as id,'名231字2' as name,'0' as age
 union all select '1' as id,'名字333' as name,'1' as age 
 union all select '9' as id,'名字3' as name,'9' as age
union all select '11' as id,'名字A' as name,'99' as age 
)) 
 where row_number_column = 1
) T2 ON ( T2.id=T1.id) WHEN MATCHED THEN  UPDATE SET T1.name = T2.name,T1.age = T2.age WHEN NOT MATCHED THEN   INSERT (id,name,age) VALUES(T2.id,T2.name,T2.age);

2、主键冲突则跳过（去掉一个WHEN MATCHED）

MERGE INTO table_9_copy T1 USING (
select id,name,age from (
select row_number() over (PARTITION by id order by age desc) row_number_column, id,name,age from (
select '0' as id,'名231字2' as name,'0' as age
 union all select '1' as id,'名字333' as name,'1' as age 
 union all select '9' as id,'名字3' as name,'9' as age
union all select '11' as id,'名字A' as name,'99' as age 
)) 
 where row_number_column = 1
) T2 ON ( T2.id=T1.id) WHEN NOT MATCHED THEN   INSERT (id,name,age) VALUES(T2.id,T2.name,T2.age);

3、临时表方式拼装 MERGE INTO SQL 语句

临时表方式拼装 MERGE INTO SQL 语句
      MERGE INTO table_9_copy T1 USING (select id,name,age from (
        select row_number() over (PARTITION by id order by age desc) row_number_column,
        id,name,age from table_9_copy_XXXXXXX)
        where row_number_column = 1
        ) T2 ON ( T1.id=T2.id) WHEN MATCHED THEN UPDATE SET T1.name=T2.name,T1.age=T2.age where T2.age > T1.age
      WHEN NOT MATCHED THEN   INSERT (id,name,age) VALUES(T2.id,T2.name,T2.age);