SQLTeam.com | Weblogs | Forums

Complex Delete Statement

Well, it's complex for me.

I want to delete rows from Table 1 where that QuoteID exists in Table 2... IF Table 2s JobProcessedDate < '2020-08-01'.
Then I want to further delete from Table 1 where that QuoteID exists in Table 2 and Table 2s quoStatus is NOT IN ('Active', 'Hold').

Can this be done in 1 statement?
Thanks.

Sure.

DELETE FROM t1
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2 ON t2.QuoteID = t1.QuoteID AND 
    (t2.JobProcessedDate < '20200801' OR t2.quoStatus NOT IN ('Active', 'Hold'))
1 Like

Should that be an AND

Thanks, ScottPletcher. Why are TWO FROM clauses required? I need to understand that part. It DID work.

The second FROM allows the INNER JOIN to be used. This is a special MS extension and not standard SQL syntax. But it's nice because it allows the syntax to look like a normal JOIN after the DELETE.

Actually the first FROM is optional in a DELETE. So we could write it like:

DELETE t1
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2 ON t2.QuoteID = t1.QuoteID AND 
    (t2.JobProcessedDate < '20200801' OR t2.quoStatus NOT IN ('Active', 'Hold'))
2 Likes