DELETE Statement with subquery strange behavior

Question

I have 2 tables with which I'm executing a delete statement. When I select a column with an invalid column name it throws an error. But when I use the column name of the first table it just deletes the records.

//Query:

CREATE TABLE FOO (ID INT)
GO
CREATE TABLE FOOBAR (NUMBER INT)
GO

INSERT INTO FOO VALUES(1)
INSERT INTO FOO VALUES(2)
INSERT INTO FOO VALUES(3)
INSERT INTO FOOBAR VALUES(1)
GO

-- The following will raise an error
SELECT ID FROM FOOBAR WHERE NUMBER = 1
GO
--Server: Msg 207, Level 16, State 3, Line 1
--Invalid column name 'ID'.


-- The following statement will delete the entire table
DELETE FROM FOO WHERE ID IN
(SELECT ID FROM FOOBAR WHERE NUMBER = 1)
GO
--(3 row(S) affected)

When I used the Alias name it worked fine. Is it a bug or something else?

How does this happen?

DELETE FROM FOO WHERE ID IN (SELECT Number FROM FOOBAR WHERE NUMBER = 1) GO — Dinesh Reddy Alla, Jul 25 '14 at 09:56
Please read [Qualifying Column Names in Subqueries](http://technet.microsoft.com/en-us/library/ms178050(v=sql.105).aspx): " If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query" — Damien_The_Unbeliever, Jul 25 '14 at 10:48

Giannis Paraskevopoulos · Accepted Answer · 2014-07-25T09:47:40.370

2

In the following statement the inner select will get the column ID from table FOO and not from table FOOBAR.

DELETE FROM FOO WHERE ID IN
(SELECT ID FROM FOOBAR WHERE NUMBER = 1)

In order to make this more obvious you could try adding Alliases to the tables and do a select instead of a delete, just to see the results:

SELECT * FROM FOO F WHERE ID IN
(SELECT F.ID FROM FOOBAR FB WHERE FB.NUMBER = 1)

edited Jul 25 '14 at 09:47

answered Jul 25 '14 at 09:41

Giannis Paraskevopoulos

18,261
1
49
69

score 0 · Answer 2 · answered Jul 25 '14 at 09:46

There is no column called ID in the table FOOBAR. It has one column, and it is called NUMBER. Your select statement should look like:

SELECT NUMBER FROM FOOBAR WHERE NUMBER = 1;

To complete the delete where NUMBER in FOOBAR equals ID in FOO looks like:

DELETE FROM FOO WHERE ID IN
(SELECT NUMBER FROM FOOBAR WHERE NUMBER = 1);

DELETE Statement with subquery strange behavior

2 Answers2