Why is SQL Server not catching this? Typo in subquery causes accidental delete

Question

I have a query with a subselect where I mistyped a column (id1 in the subselect). If the query is executed separatly SQL Server throws an error, which is correct and expected. However if executed as a subquery in the below context the typo is not catched - and in this case my table is cleared. Is this intented behaviour ?

CREATE TABLE #a1 (id1 INT);
CREATE TABLE #a2 (id2 INT);

INSERT INTO #a1 (id1) VALUES (1), (2), (3);
INSERT INTO #a2 (id2) VALUES (1);

DELETE FROM #a1 WHERE id1 = (SELECT id1 FROM #a2 WHERE id2 = 1);

SELECT * FROM #a1 -- wow, the table is now empty

I expected an error to be thrown, instead the delete query was interpreted as

DELETE FROM #a1 WHERE id1 = id1.

duplicate of [sql server 2008 management studio not checking the syntax of my query](https://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query) — underscore_d, Nov 12 '19 at 15:07

score 2 · Answer 1 · answered Nov 12 '19 at 15:08

2

Yes, this is intended behavior because your query explicitly looks like this:

DELETE FROM #a1 WHERE id1=(SELECT #a1.id1 FROM #a2 WHERE #a2.id2=1);

id1 is known in subquery because it belongs to #a1 and because for every row you select the same value exactly your receive:

delete from #a1 where id1=id1

But for subquery only (i.e. without delete section) - id1 is not known, so it shows error as expected.

answered Nov 12 '19 at 15:08

Michael Kokorin

474
2
8

yes, nearly the same - the subselect has to return at least one row however. – Peter Sawatzki Nov 12 '19 at 15:22

GMB · Accepted Answer · 2019-11-12T15:14:36.400

The columns from the table declared in the outer query are available in the inner query. That's the same logic that comes into play when you build a correlated subquery.

So in this condition:

WHERE id1 = (SELECT id1 FROM #a2 WHERE id2=1)

The subquery returns as many recods as there are in table #a2, with id1 coming from the outer query. As long as there is a unique record #a2 that satisfies condition id2 = 1, this is actually equivalent to WHERE id1 = id1.

If you had prefixed the column names with table aliases, you would have received an error:

DELETE FROM #a1 t1 WHERE t1.id1 = (SELECT t2.id1 FROM #a2 t2 WHERE t2.id2 = 1);

that should teach me to always use table aliases. I accidently cleared a table with this mistakes with 1.2 Mio rows today, grr. — Peter Sawatzki, Nov 12 '19 at 15:23

score 0 · Answer 3 · edited Nov 12 '19 at 15:18

0

id1 is in table A1.

Change the delete query to a select and you will see.

edited Nov 12 '19 at 15:18

underscore_d

6,309
3
38
64

answered Nov 12 '19 at 15:11

H20rider

2,162
5
29
47

Why is SQL Server not catching this? Typo in subquery causes accidental delete

3 Answers3