May 30, 2019 at 5:04 pm
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.
May 30, 2019 at 7:09 pm
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?
May 30, 2019 at 10:47 pm
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
June 3, 2019 at 10:44 am
Hey Auto, thank you very much for that script, works great.
All the best
June 3, 2019 at 5:18 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply