10g或者以上版本用merge into merge into a --要插入修改的表 using b--用来参考的表 on (a.id=b.id)--匹配条件 when matched then update --update不要set when not matched then insert --不要into
恩 用merge into可以实现
我没有用来参考的表b。UPDATE也必须要接SET
为何不要set?? create table mergetest(id number primary key,name varchar(20)); create table newmergetest(id number primary key,name varchar(20)); insert into newmergetest values(1,'first'); insert into newmergetest values(2,'second');insert into mergetest values(1,'one') insert into mergetest values(2,'two'); insert into mergetest values(3,'three');MERGE INTO newmergetest m using (select * from mergetest) t ON (m.id = t.id) WHEN MATCHED THEN UPDATE SET m.name = t.name WHEN NOT MATCHED THEN INSERT VALUES (t.id,t.name) ;SQL> select * from newmergetest; ID NAME ---------- -------------------- 1 one 2 two 3 three
shuo shuo qing kuang ~
如: 表名:TABLE1 ID NAME DESC MEMO 1 AA CC BB 2 BB DD FF 现在要批量操作SQL语句: 如果有一条记录ID是3,则应该新增一条记录, 如果有一条记录ID是2,则应该是对原ID为2的记录进行修改。就是这种情况,不知道高人们明白了没有。
merge into table1 a using(select &id id,.... from dual) b on(a.id=b.id) when matched then update set a.col=b.col... when not matched then insert(a.id,....) values(b.id....) --orcreate or replace procedure p_dmltb(v_id table1.id%type....) as num number; begin select count(*) into num from table1 where id=v_id; if num=0 then insert into table1 values(v_id......); else update table1 set .... where id=v_id; end if; commit; end; --
merge into a --要插入修改的表
using b--用来参考的表
on (a.id=b.id)--匹配条件
when matched then
update --update不要set
when not matched then
insert --不要into
create table mergetest(id number primary key,name varchar(20));
create table newmergetest(id number primary key,name varchar(20));
insert into newmergetest values(1,'first');
insert into newmergetest values(2,'second');insert into mergetest values(1,'one')
insert into mergetest values(2,'two');
insert into mergetest values(3,'three');MERGE INTO newmergetest m
using (select * from mergetest) t
ON (m.id = t.id)
WHEN MATCHED THEN
UPDATE SET m.name = t.name
WHEN NOT MATCHED THEN
INSERT VALUES (t.id,t.name) ;SQL> select * from newmergetest; ID NAME
---------- --------------------
1 one
2 two
3 three
表名:TABLE1
ID NAME DESC MEMO
1 AA CC BB
2 BB DD FF
现在要批量操作SQL语句:
如果有一条记录ID是3,则应该新增一条记录,
如果有一条记录ID是2,则应该是对原ID为2的记录进行修改。就是这种情况,不知道高人们明白了没有。
when matched then
update set a.col=b.col...
when not matched then
insert(a.id,....) values(b.id....)
--orcreate or replace procedure p_dmltb(v_id table1.id%type....)
as
num number;
begin
select count(*) into num from table1 where id=v_id;
if num=0 then
insert into table1 values(v_id......);
else
update table1 set .... where id=v_id;
end if;
commit;
end;
--