I have created two tables, T1 and T2 with one column each, abc and xyz respectively. I have inserted 2 rows (numeric values 1 and 2) in each table.
When I run the command "select abc from t2"
, it throws an error saying that column abc does not exist in the table T2. However, when I run the command "delete from t1 where abc in (SELECT abc from t2);"
, 2 rows are deleted.
Shouldn't the delete fail as I have used the same statement which failed in the sub-query?
create table t1 (abc number); --Table created
create table t2 (xyz number); --Table created
insert into t1 values (1); --One row inserted
insert into t1 values (2); --One row inserted
insert into t2 values (1); --One row inserted
insert into t2 values (2); --One row inserted
SELECT abc from t2; --ORA-00904 -> Because column abc does not exist in t2
delete from t1 where abc in (SELECT abc from t2); --2 rows deleted
If you use the table names as alias to make sure table t2 column is getting selected, you will get the error i.e.
delete from t1 where abc in (SELECT t2.abc from t2); --ORA-00904
Your original query is not failing because it's using abc
column of table t1
since table t1
is visible in the subquery.