Oracle: INSERT values from SELECT...JOIN, SQL Error: ORA-00947: not enough values

Question

I'm trying to do the following:

INSERT INTO MyTable(a, b, c)
SELECT a FROM source1
JOIN source2 ON ...

Where source2 contains columns B and C.

However Oracle doesn't seem to like this and is telling me "SQL Error: ORA-00947: not enough values".

Am I doing something wrong here? Is this syntax even possible? Or do I have to rewrite it as:

SELECT a, b, c FROM source1, source2 WHERE ....

Thanks!

Answer 1

Use as many identifiers in the SELECT clause as in the INSERT clause, as in:

INSERT INTO MyTable(a, b, c)
SELECT s1.a, s2.b, s2.c FROM source1 s1
  JOIN source2 s2 ON ...

Answer 2

The select needs to return the same number of columns as you listed in the INSERT statement.

So: yes, you need to rewrite the query to SELECT a,b,c FROM ...