deleting multiple records from two tables

Question

I have two tables called TableA and TableB.

TableA has the following fields:

TableA_ID
FileName

TableB has the following fields:

TableB_ID
TableA_ID
CreationDate

There is a foreign key link between the two tables on the TableA_ID field

I need to delete records from both tables. I need to look at the “CreationDate” on TableB and if it’s after a certain date, delete that record. I will also need to delete the record in TableA with the same TableA_ID as the record in TableB

There may be several records in TableB that use the TableA_ID (a one to many relationship). So I can’t delete the record in TableA if entries in TableB still use it.

I know this can’t be done in a single statement but am happy to do it in a transaction. The problem I have is I’m not sure how to do this. I’m using MS SQL server 2008. I don’t want to use triggers if possible.

Answer 1

Can there be records in TableA with no matching record in TableB? If not, then we know after we delete from TableB, we can delete any non-matching records in TableA:

begin transaction
delete from TableB
where CreationDate > @SomeDate

delete from TableA
where TableA_ID not in (select TableA_ID from TableB)
end transaction

Otherwise:

begin transaction
-- Save the TableA_IDs being deleted:
select distinct TableA_ID
into #TableA_Delete
from Table_B
where CreationDate > @Somedate

-- Depending on the expected size of #TableA_Delete, you may want 
-- to create an index here, to speed up the delete from TableA.

delete from TableB
where CreationDate > @SomeDate

delete from TableA
where TableA_id in (select TableA_Id from #TableA_Delete)
and TableA_id not in (select TableA_id from TableB)
commit transaction

NOTE Both above solutions need error handing added.

Also, see NYSystemsAnalyst for another method of storing the IDs temporarily.