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.