我只有两张简单的table:
test1(id, name)
和
test2(id, name)
我想在test1的基础上更新test2中的值。如果test2中不存在值,则应插入新行。
我的问题:
MERGE INTO test2 trg
USING (
select c.id
from test1 c
) src ON (src.id = trg.id)
WHEN MATCHED THEN
UPDATE
SET
trg.name = src.name
WHEN NOT MATCHED THEN
INSERT (id)
VALUES (src.id);
但是这个查询给我带来了一个错误:
sql错误:ora-00904:“src”。“name”:标识符00904无效。00000-“%s:无效标识符”
为什么会这样?
table:
create table test1
(
id number,
name varchar(10)
)
create table test2
(
id number,
name varchar(10)
)
insert into test1(id, name)
select 1, '1' from dual
union all select 2, '2' from dual
union all select 3, '3' from dual
union all select 4, '4' from dual
union all select 5, '5' from dual
union all select 6, '6' from dual
union all select 7, '7' from dual
union all select 8, '8' from dual
union all select 9, '9' from dual
union all select 10, '10' from dual
commit;
insert into test2(id, name)
select 20, '20' from dual
union all select 21, '21' from dual
union all select 22, '22' from dual
commit;
2条答案
按热度按时间kokeuurv1#
svmlkihl2#
您在列表中选择的唯一列
src
将调用merge语句的子查询ID
. 您还需要选择NAME
子查询中的列: