Oracle 12c-插入到选择查询中的歧义列,ORA-00918

I am trying to execute multiple insert with single statement to achieve this I am using Insert into select statement. But I am facing when two columns have same value in insert. Error message that I am getting is ORA-00918: column ambiguously defined.

询问

INSERT INTO sample (
    HOST,
    TOTAL_PING,
    TOTAL_UNAVAILABLE_PING
)

SELECT * FROM (

    SELECT 'FR3158-73-1',
    82,
    82
    FROM DUAL
    UNION ALL

    SELECT 'FR3158-76-2',
    80,
    10
    FROM DUAL
)

如果我将一个值更改为某些值,则在第一条选择语句中存在问题,其中两个值分别为82和82。即使列值相同,我也不知道如何使这项工作。

- - 更新 - -

表定义

CREATE TABLE sample
(
  ID                      NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
  HOST                    VARCHAR2(15 BYTE),
  TOTAL_PING              INTEGER,
  TOTAL_UNAVAILABLE_PING  INTEGER,
  ADDED_ON                TIMESTAMP(6)          DEFAULT systimestamp
);
评论
  • 回眸醉倾城
    回眸醉倾城 回复

    Remove select * from ( (and trailing )).

    INSERT INTO sample (HOST, TOTAL_PING, TOTAL_UNAVAILABLE_PING)
       SELECT 'FR3158-73-1', 82, 82 FROM DUAL
               UNION ALL
               SELECT 'FR3158-76-2', 80, 10 FROM DUAL