-- 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;
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
你看看merge的语法就知道了,一个是源另一个是目标,只有两个表,哪来的第三张表?
merge into a using b on 。 when matched then update set ..... --这里只能对a update when not matched then insert ......-- 这里只能对a insert
不可以,merge的正确用法是可以对1个表同时进行插入、删除、更新中的多种操作。
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;
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
using b
on 。
when matched then
update set ..... --这里只能对a update
when not matched then
insert ......-- 这里只能对a insert