Possible bug in SQL server on DELETE?

Weiwu Xie 26 Reputation points
2021-08-26T16:20:24.493+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
0 comments No comments
{count} vote

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-08-26T17:06:35.547+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-08-26T17:21:54.023+00:00

    As Tom said, this is working as intended.

    Please see:
    https://learn.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.

    0 comments No comments