语法:
MERGE INTO table_name t1
USING (table|view|sub_query) t2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET t1.a = 'value1',
t1.b = 'value2'
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
场景:
查询T1表中是否含有字段a = 'ZHANGSAN'的人,有的话更新这条数据,没有即插入!
以下为实例:
MERGE INTO T T1
USING (SELECT 'ZHANGSAN' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
注意:
如果不懂Merge语句的原理,Merge语句是一条比较危险的语句,特别是在您只想更新一条记录的时候,因为不经意间,你可能就把整表的数据都Update了一遍.特别要注意USING + ON 后面的语句和条件!!!