Possible parser bug in DELETE FROM .. FROM syntax

Question

Consider this schema:

create table t (i int);

And then, this query:

delete from t from t t2 where t.i = t2.i;

This produces an error

SQL Error [4104] [S0001]: The multi-part identifier "t.i" could not be bound.

Though I don't see anything wrong with it. These work:

delete from t from (select * from t) t2 where t.i = t2.i;
with t2 as (select * from t) delete from t from t2 where t.i = t2.i;

The SQL Server @@version is:

Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64)
Jul 25 2020 11:26:55
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 18.04.4 LTS) <X64>

Answer 1

This is working as designed. In your query that gets the error, you give the table t the alias t2. That means everywhere in your query t will now be known as t2. So when it sees the reference to t in the WHERE clause, it no longer refers to anything, so you get the error.

The other two statements that work you rename something else as t2 (in one case the derived table (select * from t) and in the other it's the cte. So you didn't rename the table t in the delete from t, so the query still knows the reference to table t.

If you wanted two references to the table t, one called t and the other t2, you could do

delete from t from t cross join t t2 where t.i = t2.i;

Then you would have two references to t, one called t and the other t2. And that is syntactically correct.

Tom