Oracle SQL中的Merge用法

Merge语句可以用来插入或更新数据，取决于数据是否已经存在。

语法结构：

MERGE INTO table_name --目标表，待更新
USING table_name --源表/视图
ON (condition) --条件语句，若为真，则记录更新；若为假，则记录插入
WHEN MATCHED THEN update_clause --条件为真时，执行语句
DELETE where_clause
WHEN NOT MATCHED THEN insert_clause
[LOG ERRORS log_errors_clause reject_limit <integer | unlimited> ]

例子：
新建两个表：customer，customer_import

图片.png

执行语句：

MERGE INTO customer c
USING customer_import i
ON (c.customer_id = i.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = i.first_name,
c.last_name = i.last_name,
c.address_state = i.address_state,
c.email_address = i.email_address
WHEN NOT MATCHED THEN
INSERT (c.customer_id, c.first_name, c.last_name, c.address_state, c.email_address)
VALUES (c.customer_id, c.first_name, c.last_name, c.address_state, c.email_address);

执行后的customer表：

图片.png