SQL Server - Delete records from multiple related tables

Question

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 ?

Set up a foreign key constraint (w/ cascading deletes) when creating the Response table. That's what they're for! — jleach
– jleach, Commented Apr 10, 2018 at 15:27

marc_s · Accepted Answer · 2018-04-10 16:13:40Z

2

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;

edited Apr 10, 2018 at 16:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 10, 2018 at 15:31

jleach

7,8904 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paul Williams Over a year ago

Transacting the multiple deletes might be to maintain data integrity.

user3198688 Over a year ago

will we face any issue in future if we go without using foreignkey constraint and delete using multiple deletes ?

jleach Over a year ago

Data integrity is the only real issue, but that can be a very big issue if left unchecked. FK constraints are typically much preferred, but a sound education on database design would arm you with the ability to best answer your question (e.g., hard for me to say for sure)

user3198688 Over a year ago

Thank you, in that case i feel it is best to create a foreign key constraint on the table..

Collectives™ on Stack Overflow

SQL Server - Delete records from multiple related tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related