Oracle中使用merge into语句:数据存在则更新,不存在则插入。
merge into TEST1 t1
using DUAL
on ( t1.ID= 5)
when matched then
update
set t1.NAME = 'xxx'
when not matched then
insert (id,NAME)
values (5,'xxx' );
dual是一张临时表,也可以自己构建,例如:
merge into TEST1 t1
using (select 5 as ID from dual) t2
on ( t1.ID= t2.ID)
when matched then
update
set t1.NAME = 'xxx'
when not matched then
insert (id,NAME)
values (5,'xxx' );
还可以联合其他表。联合表的情况(如下语句),表t1会更新所有符合条件的行。
merge into TEST1 t1
using (select id from TEST2 where money > 90) t2
on ( t1.ID= t2.ID)
when matched then
update
set t1.NAME = 'xxx'
when not matched then
insert (id,NAME)
values (5,'xxx' );
mysql:数据存在则更新,不存在则插入
这个是根据主键存在来判断是更新还是插入。
INSERT INTO test(id,name) VALUE(2,'小李') ON DUPLICATE KEY UPDATE name='小李';