CODEWITHSUNDEEP
sqlsql-server-2012
user3198688
user3198688

Reputation: 305

SQL Server - Delete records from multiple related tables

I have 2 related tables called tblResponse and tblUser, I want to delete records from tblUser which have a value of inactive in the status column.

In the tblResponse table, I also want to delete all these records whose UserId from tblUser have been deleted.

Each UserId potentially has multiple responses in tblResponse table, it needs to delete all of them.

This jobs needs to run everyday.

 DELETE A
 FROM tblUser A
 INNER JOIN tblUser U ON UserId = EmployeeID
 WHERE UserStatus = 'Inactive'

In the above query UserId is from tblUser and EmployeeID is from tblResponse.

How can I delete the data from 2 tables ?

Upvotes: 0

Views: 2097

Answers (1)

jleach
jleach

Reputation: 7792

The usual way to handle this is to set up the tables with a foreign key constraint, where the ON DELETE is set to CASCADE:

CREATE TABLE Users 
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    ... other fields
)

CREATE TABLE Responses 
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    UserID INT NOT NULL REFERENCES dbo.Users (ID) 
        ON UPDATE CASCADE  -- when UserID updated, update this UserID also
        ON DELETE CASCADE, -- when User deleted, delete these rows also
    ... other fields
)

This is of course the preferred method because it instructs the database to maintain its own integrity without requiring the application layers to handle it.

Occasionally this cannot be done though, either due to pre-existing tables with bad data (where we should then consider cleaning the data then applying the constraint), or possible circular constraints, in which case you have to use two delete statements:

-- remove the associated responses first
DELETE r
FROM Users AS u
INNER JOIN Responses AS r ON u.ID = r.UserID
WHERE u.Active = 0;

-- then delete your users
DELETE FROM Users WHERE Active = 0;

Upvotes: 2

Related Questions