oracle merge sql太长,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 ]

例子：

新建两个表：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