?

Log in

No account? Create an account
Trevor Stone's Journal
Those who can, do. The rest hyperlink.
SQL Server Can't Alias Tables in Delete 
22nd-Apr-2008 10:04 am
farts sign - Norway
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 
22nd-Apr-2008 07:10 pm (UTC)
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.

22nd-Dec-2010 01:19 pm (UTC) - 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
This page was loaded Apr 25th 2018, 1:07 am GMT.