您可以使用MERGE INTO
语句对一个表同时进行INSERT
和UPDATE
等操作。
限制条件
不支持目的表包含子查询。
不支持
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);
文档内容是否对您有帮助?