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
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