How do I delete results from a select join statement-CodePudding

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'

CodePudding user response：

I guess what you want is filter out the result not needed, you can use "Not in" in your select query, for example filter out 'id1' and 'id2' like following:

Select t2.TransaationID from TableA as t1 INNER JOION 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 
and t1.TransactionID not in ('id1','id2')

CodePudding user response：

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