PolarDB:MERGE INTO

Limits

• The table on which you perform operations cannot have subqueries.

• The UPDATE and DELETE statements cannot contain a WHERE clause.

  Note

  You can modify a WHERE clause to WHEN MATCHED [ AND condition ].

• The serializable isolation level is not supported.

• We recommend that you do not perform concurrent updates on the destination table while you execute the MERGE INTO statement. This may cause serious consequences: The execution duration is more than five times longer than that of non-concurrent updates.

• If the MERGE INTO statement contains only the MATCHED THEN clause but not the NOT MATCHED clause, we recommend that you use the UPDATE statement instead of the MERGE for better performance.

Syntax

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

Example

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);