question

WeiwuXie-3597 avatar image
1 Vote"
WeiwuXie-3597 asked TomPhillips-1744 edited

Possible bug in SQL server on DELETE?

with code below will delete data in test2 which is not expected
1) there is a clear syntax error in the subquery but not detected by intellisense
2) the query run fine and deleted the data in test2

 create table test1 (RYear varchar(6))
    
 insert into test1 (RYear)
 values ('202107')
    
 create table test2 (RMonth varchar(6))
    
 insert into test2(RMonth)
 values ('202108')
    
 select * from test1
    
 select * from test2
    
    
 delete test2
 where RMonth in (select RMonth from test1)
    
 select * from test2



sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered TomCooper-6989 edited

This is working as designed. When you have a subquery like
(select MyColumn From MyTable)
SQL looks for a column named in the table MyTable. If it finds one, it uses that column. But if Mytable doesn't contain a column named MyColumn, SQL will look at the outer table. If it finds one there, SQL will use it. If it doesn't find one there, then you get a syntax error.

So since RMonth exists in test2, but not in test1, your query is equivalent to

  delete test2
  where test2.RMonth in (select test2.RMonth from test1)

And, of course, test2.Month can be found in test2.Month, so the row is deleted.

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

As Tom said, this is working as intended.

Please see:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15#qualifying

Important

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.