oracle insert缺少set,oracle安插时，若表中不存在该信息则插入，否则更新主键外的其他信息...

oracle插入时，若表中不存在该信息则插入，否则更新主键外的其他信息

--测试数据

create table table1(id varchar2(100),name varchar2(1000),address varchar2(1000));

insert into table1(id,name,address)values('01001','影子','河北') ;

commit;

--插入

merge into table1 t1

using (select '01002' id,'影子' name,'河北' address from dual) t2

on (t1.id = t2.id)

when matched then

update set t1.name = t2.name, t1.address = t2.address

when not matched then

insert values (t2.id, t2.name,t2.address);

commit;

--查询结果

select * from table1

01001 影子 河北

01002 影子2 辽宁

--更新

merge into table1 t1

using (select '01001' id,'不是影子' name,'山西' address from dual) t2

on (t1.id = t2.id)

when matched then

update set t1.name = t2.name, t1.address = t2.address

when not matched then

insert values (t2.id, t2.name,t2.address);

commit;

--查询结果

select * from table1

01001 不是影子 山西

01002 影子2 辽宁

--删除测试数据

drop table table1;

安全一点的做法

begin

insert into Table (ID,Value,..) values(1001,'222',...);

commit;

exception

when dup_val_on_idx then

update Table set value = '222' where id = 1001;

commit;

when others then

Rollback;

end;