Search code examples
sqlwhere-clausesql-delete

How do I delete results from a select join statement

How do I delete the result from the Select Join statement below.

SELECT 
   t2.TransactionID
FROM
    TableA AS t1
INNER JOIN 
    TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
WHERE
    t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'
Solution

  • As I understand your question, you're asking how to delete the result set from both tables. Within the context of a SQL statement, you can't. A sql delete statement only acts on a single table. You'll have to do 2 deletes, first deleting the 'TableA' record, then the 'TableB' record.

    delete from tableA where transctionID in
(select transactionID from tableB where accountingDate between ...);

delete from TableB where accountingDate between ...;