MERGE INTO

限制条件

• 不支持目的表包含子查询。

• 不支持UPDATE和DELETE中包含WHERE语句。

  说明

  可以将WHERE语句修改为WHEN MATCHED [ AND condition ]。

• 不支持串行化隔离级别。

语法

polardb=# \h merge
Command:     MERGE
Description: insert, update, or delete rows of a table based upon source data
Syntax:
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]

where data_source is

{ source_table_name |
  ( source_query )
}
[ [ AS ] source_alias ]

and when_clause is

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING }
}

and merge_insert is

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
           } [, ...]

and merge_delete is

DELETE

示例

merge into test1 using test2 
	on (test1.id = test2.id) 
when matched then
	update set test1.id = test1.id + 1
when not matched then
	insert values(test2.id+1);