需求:
从A表中查询出来的多条数据需要更新到B表中;
若存在该条记录则更新,不存在该条数据则插入
实现:
在SQL Server中的语法如下:
if exists(select 1 from T where T.a='1001' )
update T set T.b=2 Where T.a='1001'
else
insert into T(a,b) values('1001',2);
在Oracle中的语法如下:
方法1.
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
方法2.
伪表操作:
MERGE INTO T T1
USING (SELECT '1001' 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);
纯干货,无废话,多多支持!