How to delete records from multiple tables?

Question

Post reply

How to delete records from multiple tables?

rkelly58

SSCrazy

Points: 2151
More actions
May 30, 2019 at 5:04 pm

#3648663

Hi,
I have four tables:
Table1 is data which includes a submitted_Date with a record ID
Table2,3,4 also have data, which include record ID (The record ID is key and matches the record ID in Table1)
Using SQL, how do I delete all records over 3 years old in Table1 and then delete the corresponding data in Table2 as well?
I came up with this to delete the records in Table1:
USE [MyDB]
GO
DELETE FROM [dbo].
[Table1]
WHERE Submitted_date < DATEADD(YEAR, -3, GETDATE())
GO
But no idea how I encorporate deletion of corresponding ID records in Table 2,3,4 using the record ID of the records being deleted in table 1
Any help appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Set up cascading deletes between the parent and child tables, and just delete from the parent table?

Or use a trigger to get the IDs from the deleted virtual table and use that to delete from the child/related tables?

autoexcrement SSCertifiable Points: 5956 More actions · Answer 2

BEGIN TRANSACTION 

SELECT PrimaryKey 
INTO #MyTempTable
FROM Table1
WHERE Submitted_date < DATEADD(YEAR, -3, GETDATE());

DELETE Table1
WHERE EXISTS
(
     SELECT NULL
     FROM #MyTempTable
     WHERE #MyTempTable.PrimaryKey = Table1.PrimaryKey
);

DELETE Table2
WHERE EXISTS
(
     SELECT NULL
     FROM #MyTempTable
     WHERE #MyTempTable.PrimaryKey = Table2.PrimaryKey
);

DELETE Table3
WHERE EXISTS
(
     SELECT NULL
     FROM #MyTempTable
     WHERE #MyTempTable.PrimaryKey = Table3.PrimaryKey
);

DELETE Table4
WHERE EXISTS
(
     SELECT NULL
     FROM #MyTempTable
     WHERE #MyTempTable.PrimaryKey = Table4.PrimaryKey
);

DROP TABLE #MyTempTable;

END TRANSACTION

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

rkelly58 SSCrazy Points: 2151 More actions · Answer 3

Hey Auto, thank you very much for that script, works great.

All the best

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

I'd urge you not to use a temp table for that. If SQL goes down during the processing, you'll never be able to determine what the original key values were.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 5

ScottPletcher wrote:

I'd urge you not to use a temp table for that. If SQL goes down during the processing, you'll never be able to determine what the original key values were.

Wouldn't it rollback as it's wrapped in a transaction?