oracle update语句,错误为ora-00904

bxfogqkk  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(366)

我只有两张简单的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;
kokeuurv

kokeuurv1#

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)
svmlkihl

svmlkihl2#

您在列表中选择的唯一列 src 将调用merge语句的子查询 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);

相关问题