SQL Server Can't Alias Tables in Delete

[flwyd]

The following is legal syntax in SQL Server 2005:
select * from my_table t where t.foo = 1

The following is not legal syntax in SQL Server 2005:
delete from my_table t where t.foo = 1

In this example, the table alias doesn't add anything, but it does in the following:
delete from my_table t1 where foo = 1 and not exists (select null from my_table t2 where t2.foo = 2 and t1.id = t2.id)

Table aliases are necessary to do same-table subselects and joins, and SQL Server will happily let you use them to find out what data you plan to delete, but not to actually delete that same data. I instead switched to
delete from my_table t1 where foo = 1 and id not in (select id from my_table t2 where t2.foo = 2)
which takes a really long time because it does the subselect for every row matching foo = 1.

If Microsoft's implementation of SQL were compared to Microsoft's implementations of JavaScript/DOM/CSS, I suspect the latter would have way more annoying quirks, but the former has way more annoying basic problems. The fact that my CD started skipping while sorting this out (and probably need to be returned to the store) adds to the general grumpiness of the moment.

Comments

[bike4fish.livejournal.com]

I was recently irked by MySql's use of the non-standard "ENUM" data type to restrict values allowed in a column. So irked, in fact, that I switched to using Derby (for development, anyway). Derby uses a standard check constraint; MySql ignores check constraints.

CAN alias table in delete

[(Anonymous)]

Hi Trevor,

SQL Server 2005 can alias tables in a delete statement. If you look thourough enough at the documentation for the delete statement you will see that there are two FROMs. The first specifies the table from where data should be deleted, the second specifies a joined table. I must admit that I didn't understand exactly yet how this works, but it works!

delete /*from*/ my_table from my_table t1 where foo = 1 and not exists (select null from my_table t2 where t2.foo = 2 and t2.id = t1.id)

You can also write it like this, using the alias in the first FROM:
delete /*from*/ t1 from my_table t1 where foo = 1 and not exists (select null from my_table t2 where t2.foo = 2 and t2.id = t1.id)

Greetings,
Klaus Triendl