Skip to Main Content

INSERT ALL - Multiple table insert issue

KarkiOct 14 2013 — edited Oct 14 2013

There are three tables A, B, C. A has three rows. It has record of table B + 1 row. B has two rows, has record of table C + 1 row. C has one row.

SQL> SELECT * FROM A;

        C1         C2                                                          

---------- ----------                                                          

         1          1                                                          

         2          2                                                          

         3          3                                                          

SQL> SELECT * FROM B;

        C1         C2                                                          

---------- ----------                                                          

         1          1                                                          

         2          2                                                          

SQL> SELECT * FROM C;

        C1         C2                                                          

---------- ----------                                                          

         1          1                                                          

I need to write a single query which make rows equal in all tables. I use INSERT ALL WHEN INTO method but get following errors:

SQL> INSERT ALL

  2  WHEN A.c1 NOT IN ( SELECT c1 FROM B) THEN

  3  INTO B VALUES(A.c1, A.c2)

  4  WHEN A.c1 NOT IN (SELECT c1 FROM C)  THEN

  5  INTO C VALUES(A.c1, A.c2)

  6  SELECT c1, c2 FROM A;

INTO C VALUES(A.c1, A.c2)

                    *

ERROR at line 5:

ORA-00904: "A"."C2": invalid identifier

However when I change columns names of all table and change INSERT ALL clause. I was able to execute it:

SQL> select * from a;

        A1         A2                                                          

---------- ----------                                                          

         1          1                                                          

         2          2                                                          

         3          3                                                          

SQL> select * from b;

        B1         B2                                                          

---------- ----------                                                          

         1          1                                                          

         2          2                                                          

SQL> select * from c;

        C1         C2                                                          

---------- ----------                                                          

         1          1                                                          

SQL> INSERT ALL

  2  WHEN A1 NOT IN ( SELECT B1 FROM B) THEN

  3  INTO B VALUES(A1, A2)

  4  WHEN A1 NOT IN (SELECT C1 FROM C)  THEN

  5  INTO C VALUES(A1, A2)

  6  SELECT A1, A2 FROM A;

3 rows created.

My question is: What is the error with previous syntax? Is there any limitation of INSERT ALL statement.

Comments
Post Details
Added on Oct 14 2013
6 comments
310 views