8

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

Community
  • 1
  • 1
Orangecrush
  • 1,970
  • 2
  • 15
  • 26
  • 3
    I guess it is because `abc` in the `DELETE` statement means `abc` in `t1` – luiges90 Dec 26 '12 at 06:08
  • try running a select in your last query to confirm which are the rows that get returned, then you ll have a better idea which abc values were being returned – Hanky Panky Dec 26 '12 at 06:08
  • Thanks for your replies. When I use the alias "delete from t1 where abc in (SELECT t2.abc from t2), it throws an error. But shouldn't it throw an error even without that as I have used "select abc from t2"? – Orangecrush Dec 26 '12 at 06:14

2 Answers2

11

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.

Yogendra Singh
  • 33,927
  • 6
  • 63
  • 73
1

Your Delete statement is working because of the abc column name which u have used in Where condition. sub query is executing based on the where condition column, becz we d't use the table alias name.

if u see these queries

select * from t1 where abc in (SELECT abc from t2); -- it 'll give 2 rows

select * from t1 where abc in (SELECT 1 from t2); -- it 'll give 1 row

select * from t1 where abc in (SELECT 2 from t2); -- it 'll retrieve 2nd row

select * from t1 where abc in (SELECT 3 from t2); -- w't get d data

select * from t1 where abc in (SELECT hg from t2); -- Invalid Identifier

Dileep
  • 624
  • 3
  • 10
  • 20