How to delete records from multiple tables?

1

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.

2

Capture the IDs being deleted using the OUTPUT clause, then use that table to delete from the other tables:

IF OBJECT_ID('tempdb.dbo.#table1_recordIDs') IS NOT NULL
    DROP TABLE #table1_recordIDs
CREATE TABLE #table1_recordIDs (
    recordID int NOT NULL
    )
CREATE CLUSTERED INDEX table1_recordIDs__CL ON #table1_recordIDs ( recordID );

DELETE FROM [dbo].[Table1]
OUTPUT DELETED.recordID INTO #table1_recordIDs ( recordID )
WHERE Submitted_date < DATEADD(YEAR, -3, GETDATE())