views:

136

answers:

3

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.

A: 

Referential integrity should delete the child rows when the parent is deleted. Make sure you have cascade deletes enabled.

Otherwise you would have to put in two delete statements one for the child records and one for the parent record.

DELETE FROM TableB INNER JOIN TableA ON TableA.TableAID = TableB.TableAID WHERE CreationDate >= SomeDate

DELETE FROM TableA WHERE TableAID=SomeID
JonH
He can't depend on referential DELETE cascades because he doesn't want to delete TableB entries if they're CreationDate is OK. A cascading DELETE would wipe them out. Also, the two statements you posted delete the correct entries from TableB, but where does SomeID come from?
NYSystemsAnalyst
SomeID is just a parameter. I assume he / she has some sort of idea of what parameters are in SQL.
JonH
Plus there are many reasons not to use cascade delete. It is best not to rely on being able to set it up as it can create severe performance problems.
HLGEM
A: 

Edit: Sorry, I didn't see the part of your question stating the need to keep the TableA entries if they have multiple records in TableB. Use this instead:

/* Table valued variable to hold Table A IDs to be deleted. */
DELCARE @IDs AS table
(
    ID int
);

/* Get the TableA IDs that are subject to deletion. */
INSERT INTO @IDs (
    ID
)SELECT TableA_ID
FROM TableB
WHERE CreationDate >= @MyCreationDate)
GROUP BY TableA_ID
HAVING (COUNT(TableA_ID) = 1); /* Only get IDs that appear once in TableB */

/* Delete the TableB records. */
DELETE b
FROM TableB AS b
WHERE CreationDate >= @MyCreationDate);

/* Delete the TableA records. */
DELETE a
FROM TableA AS a
    INNER JOIN @IDs AS c ON (a.TableA_ID = c.ID);

You should wrap all of this in a transaction to ensure data integrity.

NYSystemsAnalyst
The select that populates @IDs won't work. 1) OP says nothing about there not being multiple rows in TableB that are being deleted per TableA_ID. 2) The HAVING clause happens (logically) after the WHERE clause, so if there are TableB records not being deleted, the HAVING clause won't see them, and will not count them.
Shannon Severance
1) The original post states "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." So, the TableA records cannot be deleted if there are entries in table B that are not being deleted.
NYSystemsAnalyst
+5  A: 

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.

Shannon Severance
+1 Shannon's solution solves the shortcoming of the WHERE...GROUP BY clause in my solution below.
NYSystemsAnalyst
For some reason this fails to save because of the last "Where" statement. I'll fiddle with it but can anyone spot if there is something missing ?
Retrocoder
@Retrocoder: Sorry, that last where was left over cruft and not useful. Edited to fix.
Shannon Severance