9i的话,可以使用merge,for example:
MERGE INTO table_name table1
USING table_name2 table2
ON (table1.col1=table2.col2)
WHEN MATCHED THEN
UPDATE SET
table1.col1=table2.col2,
table1.col2=table2.col3,
...
WHEN NOT MATCHED THEN
INSERT VALUES(table2.col1,table2.col2,table2.col3,...); -----
MERGE INTO table_name table1
USING table_name2 table2
ON (table1.col1=table2.col2)
WHEN MATCHED THEN
UPDATE SET
table1.col1=table2.col2,
table1.col2=table2.col3,
...
WHEN NOT MATCHED THEN
INSERT VALUES(table2.col1,table2.col2,table2.col3,...); -----
MERGE INTO bbb a
USING cc c
ON (a.id=c.id)
WHEN MATCHED THEN
UPDATE SET
a.name=c.name
WHEN NOT MATCHED THEN
INSERT VALUES(c.id,c.name);
USING a
ON (a.id=b.id)
WHEN MATCHED THEN
UPDATE SET
b.qty=a.qty+b.qty
WHEN NOT MATCHED THEN
INSERT VALUES(a,id,b.qty);
create table a(x varchar2(2),b numeric(2,0));
create table b(x varchar2(2),b numeric(2,0));我这样用哪里错了merge into a
using b on (a.x=b.x)
when matched then
update a set a.b=a.b+b.b;
when not matched then
insert into a values (b.x,b.b);
第 4 行出现错误:
ORA-00971: 缺失 SET 关键字
SP2-0734: 未知的命令开头 "when not m..." - 忽略了剩余的行。
insert values (b.x,b.b)
*
第 1 行出现错误:
ORA-00925: 缺失 INTO 关键字
merge into a
using b on (a.x=b.x)
when matched then
update set a.b=a.b+b.b;
when not matched then
insert values (b.x,b.b);SP2-0734: 未知的命令开头 "when not m..." - 忽略了剩余的行。
insert values (b.x,b.b)
*
第 1 行出现错误:
ORA-00925: 缺失 INTO 关键字