You can execute the Merge Into statement to perform operations, such as INSERT and UPDATE, on a table at the same time.

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.

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

Examples

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