Friday, May 22, 2009

The Weird Syntax of SQL DELETE From a JOIN

It is perfectly legal in most SQL dialects to DELETE records that come from a JOIN clause. However, I never remember the syntax because its kind of strange and I don't do it often.

I wrote the following example in SQL Server 2005:

delete from arledger 
from arledger
inner join arinvoice on arinvoice.arinvoiceid = arledger.invoicenumber
where arinvoice.brokersaleid = 2259




Nothing complicated. Just note line 2 where the FROM clause is repeated.

2 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Mark Richard Francis said...

I always, always, get nervous when JOINs on my DELETEs get complex.