Oracle语句错误ORA-00904

Question

我只有两张简单的桌子：

test1(id, name)

和

test2(id, name)

我想基于test2更新test1中的值。如果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”.“名称”：无效标识符00904.00000 - "%s:无效标识符“

这是为什么？

表：

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;

Answer 1

MERGE INTO test2 trg
        USING (
          select c.id,c.name
          from test1 c
        ) src ON (src.id = trg.id)
        WHEN MATCHED THEN
          UPDATE
          SET
            trg.name = src.name
        WHEN NOT MATCHED THEN
          INSERT (id,name)
          VALUES (src.id,src.name)

Answer 2

在合并语句的src子查询中选择的唯一列是ID。您还需要在子查询中选择NAME列：

MERGE INTO test2 trg
    USING (
      select c.id, c.name
      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);