Oracle 12c - Ambiguous column in Insert Into Select Query, ORA-00918

Question

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.

Query

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
)

Issue is there in first select statement where two values are 82 and 82, if I change one value to something works. I don't know how to make this work even if column values are same.

--- Updates ---

Table Definition

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

@TimBiegeleisen Question updated, I have added table definition — Touheed Khan, Feb 07 '20 at 08:38

Alex Poole · Accepted Answer · 2020-02-07T09:45:29.010

In this case you don't need the subquery - as @Littlefoot showed. But if you did, with a more complicated scenario, you can avoid the error by aliasing the column expressions in the subquery:

INSERT INTO sample (
    HOST,
    TOTAL_PING,
    TOTAL_UNAVAILABLE_PING
)

SELECT * FROM (

    SELECT 'FR3158-73-1' as host,
    82 as total_ping,
    82 as total_unavailable_ping 
    FROM DUAL
    UNION ALL

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

2 rows inserted.

The problem is that the subquery on its own gets implied column aliases, derived from the values in the first branch of the query:

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

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

'FR3158-73-         82         82
----------- ---------- ----------
FR3158-73-1         82         82
FR3158-76-2         80         10

The second and third columns are both called "82", which is the ambiguity the ORA-00918 is complaining about, from the outer select. If you add aliases that goes away:

SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping 
FROM DUAL
UNION ALL

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

HOST        TOTAL_PING TOTAL_UNAVAILABLE_PING
----------- ---------- ----------------------
FR3158-73-1         82                     82
FR3158-76-2         80                     10

so the outer query is no longer confused. Note that you only need the aliases in the first branch of the union (usually, anyway) - it doesn't hurt to have them in all branches, they'll just be ignored, but it saves a bit of typing if you're creating this manually. The actual alias names also don't matter in this case, they just have to be unique; specifically, they don't have to match the columns you're inserting into - but it makes it easier to follow if they do.

If you do it as @Littlefoot showed you don't have the intermediate result set select, so the derived names don't need to be evaluated (if they can be said to exist at all), so the ambiguity is not seen - it's purely positional.

Thanks Alex.. You have explained complete scenario in detail.. Upvote :) — Touheed Khan, Feb 07 '20 at 09:04

Littlefoot · Answer 2 · 2020-02-07T18:54:10.557

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

[EDIT, after a comment that it still doesn't work]

Well, it works, at least in my 11gXE:

SQL> select * From v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> CREATE TABLE sample
  2  (
  3    ID                      NUMBER,
  4    HOST                    VARCHAR2(15 BYTE),
  5    TOTAL_PING              INTEGER,
  6    TOTAL_UNAVAILABLE_PING  INTEGER,
  7    ADDED_ON                TIMESTAMP(6)          DEFAULT systimestamp
  8  );

Table created.

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

2 rows created.

No error on 12c either:

SQL> select * from v$version where rownum = 1;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SQL> CREATE TABLE sample
  2  (
  3    ID                      NUMBER,
  4    HOST                    VARCHAR2(15 BYTE),
  5    TOTAL_PING              INTEGER,
  6    TOTAL_UNAVAILABLE_PING  INTEGER,
  7    ADDED_ON                TIMESTAMP(6)          DEFAULT systimestamp
  8  );

Table created.

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

2 rows created.

SQL>

Now, please, prove that it doesn't work on your database.

Nice one +1. Do you know of some way to fix the exact code the OP is using? — Tim Biegeleisen, Feb 07 '20 at 08:43
Your query is showing same error that has been mentioned in my question. — Touheed Khan, Feb 07 '20 at 14:14
I edited my answer by adding examples on live databases, 11gXE and 12cR2 - no error on either of them. — Littlefoot, Feb 07 '20 at 18:54
[Error] Execution (122: 8): ORA-00918: column ambiguously defined, My DB version `Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production` — Touheed Khan, Feb 10 '20 at 07:10
Could you copy/past your SQL*Plus session by editing the original question? Don't do that as a comment, it is unreadable. I'd like to see both CREATE TABLE and INSERT INTO statements. Basically, exactly as you see in my example. — Littlefoot, Feb 10 '20 at 07:25

Marcin Badtke · Answer 3 · 2020-02-07T08:56:27.443

Try to name columns:

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

But better way is:

    INSERT INTO sample (HOST, TOTAL_PING, TOTAL_UNAVAILABLE_PING) values ('FR3158-73-1',82,82);
    INSERT INTO sample (HOST, TOTAL_PING, TOTAL_UNAVAILABLE_PING) values ('FR3158-76-2',80,10);

you avoid subquery. If you have lots of data to insert into the database consider SQL*Loader and loading from text file. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-sql-loader-concepts.html#GUID-DD843EE2-1FAB-4E72-A115-21D97A501ECC

Oracle 12c - Ambiguous column in Insert Into Select Query, ORA-00918

3 Answers3

Linked