Using aliases on table names in SQL DELETE statements
I ran into a situation where I needed to use a correlated subquery to determine the rows I wanted to delete from a table.
Unfortunately, the table on which I was running the delete was also the one from which I needed matching data to identify the rows. I had to disambiguate the table references of the inner query by using aliasing.
A SELECT (as opposed to DELETE) version of the query is something like:
SELECT * FROM MyTable AS LEFT
WHERE LEFT.Side = ‘LEFT’
AND NOT EXISTS (
SELECT * FROM MyTable AS RIGHT
WHERE RIGHT.Side = ‘RIGHT’
AND RIGHT.ToLeft = LEFT.ToRight
)
For the moment, don’t focus on what the query is trying to accomplish. Just notice that this SELECT statement relies on the aliasing of the outer table reference ("LEFT”) to disambiguate the inner query. The issue at hand is that when you change this to a DELETE statement, the syntax for the essential aliasing becomes tricky.
DELETE FROM MyTable AS LEFT …
doesn’t work!
After doing a bit of research, I found that it is possible to alias a delete, but the syntax is a bit non-intuitive. The following does work:
DELETE LEFT FROM MyTable AS LEFT …
Score one for the syntax police.