两个表结构如下:SQL> desc employee
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------- ID NUMBER(4)
NAME VARCHAR2(8)
DEP_ID NUMBER(4)
SALARY NUMBER(6)SQL> desc employee1
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------- ID NUMBER(4)
NAME VARCHAR2(8)执行一个 merge 语句老是报错:
1 merge into employee1 e1
2 using employee e2
3 on (e1.id = e2.id)
4 when matched then
5 update set
6 name = e1.name
7 when not matched then
8* insert (id, name) values(e2.id, e2.name)
SQL> /
insert (id, name) values(e2.id, e2.name)
*
ERROR 位于第 8 行:
ORA-00957: 列名重复把 insert 中的字段列表去掉就执行成功怎么回事?
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------- ID NUMBER(4)
NAME VARCHAR2(8)
DEP_ID NUMBER(4)
SALARY NUMBER(6)SQL> desc employee1
名称 是否为空? 类型
----------------------------------------- -------- ---------------------------- ID NUMBER(4)
NAME VARCHAR2(8)执行一个 merge 语句老是报错:
1 merge into employee1 e1
2 using employee e2
3 on (e1.id = e2.id)
4 when matched then
5 update set
6 name = e1.name
7 when not matched then
8* insert (id, name) values(e2.id, e2.name)
SQL> /
insert (id, name) values(e2.id, e2.name)
*
ERROR 位于第 8 行:
ORA-00957: 列名重复把 insert 中的字段列表去掉就执行成功怎么回事?
WHEN NOT MATCHED THEN
INSERT
VALUES(......)
這個時候是不需要列名的!
2 using employee e2
改成
merge into employee1 e1
2 using (select id,name from employee) e2
应该是WHEN NOT MATCHED THEN
INSERT
VALUES(......) 看不出doer_ljy 的改法的优势望指点下 ^_^
如下:
merge into employee1 e1
using employee e2
on (e1.id = e2.id)
when matched then
update set
e1.name = e1.name
when not matched then
insert (e1.id, e1.name) values(e2.id, e2.name)
merge into employee1 e1
using employee e2
on (e1.id = e2.id)
when matched then
update set e1.name = e2.name
when not matched then
insert (e1.id, e1.name)
values(e2.id, e2.name)
;