正解
[Quote=引用 9 楼 northgale 的回复:]
merge into a
using b
on 。。。。
when matched then
update set ..... --这里只能对a update
when not matched then
insert ......-- 这里只能对a insert
[/Quote]
insert可以实现
http://blog.csdn.net/zftang/article/details/6208357
insert all
when id <10 then
into test1(id,name,sex,cj)
when id >10 and id<50 then
into test2(id,name,sex,cj)
when id >50 and id<1000 then
into test3(id,name,sex,cj)
select id,name,sex,cj from test
-- Create table
create table NAMESTU
(
ID NUMBER(10),
NAGE NUMBER(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
create table NAMESTU1
(
ID NUMBER(10),
KK NUMBER(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
merge into namestu a
using dual
on (a.id = 223)
when matched then
update set a.nage = 2
when not matched then
INSERT values(223,1)
;
merge into namestu a
using dual
on (a.id = 224)
when matched then
update set a.nage = 0
when not matched then
INSERT (ID) values(223)
;
MERGE INTO NAMESTU A
USING NAMESTU1 B
ON (A.ID=B.ID)
WHEN MATCHED THEN
UPDATE SET A.NAGE=B.KK
WHEN NOT MATCHED THEN
INSERT VALUES(225,10)
;
SELECT * FROM namestu;