Delete rows from multiple tables due to data from one table

Question

I have multiple tables that I need to delete rows from and they are all contingent on data from one table. For instance, I have a Users Table and in the Users Table the Users have a UserId. I am trying to get rid of all the data that is in relation to that one user in all the other tables.

For instance, there is a Members Table and in the Members table, there is a Foreign Key relationship to the Users table so it's tied by the UserId. The Members table can have multiple members associated with the UserId (A User can be members to more than one thing so it's a one to many relationship). Then there is also a Permissions table and in the Permissions table it has a foreign key relationship to the Members table. And in that table the Members can have multiple permissions (A Member can have permissions to more than one thing is that's a one to many relationship).

What I am needing to do is delete all the rows in the Permissions table that are in relation to the all the MemberId's that are in relation to the UserId from the User's Table (i.e. - Bob has a UserId of 7 and in the Members Table he is a Member of 3 things so he has 3 MemberId's associated to his name and in the Permissions Table those 3 MemberIds also have 3 Permissions associated to those 3 MemberIds. I am needing to delete all 9 Permission rows according to those 3 MemberIds, then delete all the 3 Member rows according to the 1 UserId, and then delete the one UserId according to a UserName).

I've tried to Inner Join multiple tables and also connect them with Unions but I am having difficulty of tying all those tables down to that one UserId and carrying that data through the flow of logic.

User has a UserId of 7

User is is associated with 3 MemberId's

The MemberId is associated with multiple PermissionId's

So I need to delete all those PermissionId's (I did a WHERE statement just on the User's MemberId's), all 3 instances of the MemberId's, and the User.

I think you might be after 'cascade delete' ? This other question may help shed some light - https://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server — Navik Hiralal, May 31 '19 at 04:59
tag appropriate database name. from how many tables you wants to remove records? Can you provide some sample data from all tables with indication that which row and why they should be deleted? — mkRabbani, May 31 '19 at 05:48
@mkRabbani I have included images that hopefully will help. Thank you. — kpschwert, May 31 '19 at 11:36
So you need only to delete records from Member-Permission table.. Right? — mkRabbani, May 31 '19 at 11:39
@mkRabbani I need to delete all the rows from the Permission's table that are in relation to all the MemberIds, and those MemberId's are contingent to all the MemberId's from the Member's table in relation to the UserId, and all of this is contingent on the UserId from the User's table according to the UserName. So every row in the Permission's table that has a MemberId that is associated with the UserId from the User's table. UserId 7 row needs to be deleted, 3 rows in Members Table that are in relation to the UserId 7, and all the Permissions that are in relation to all those 3 MemberIds. — kpschwert, May 31 '19 at 11:51

mkRabbani · Accepted Answer · 2019-05-31T12:03:10.547

If you are aware of UserID that you want to delete from different table, then this is easy to handle from table Users and Members. You need to use a Sub Query when you will delete records from Permissions table. But if there are relation (PK/FK) established between tables, you need to maintain some sequence as below-

Important Note: Delete is a risky operation unless you have proper Backup. So please try to execute scripts on test data first.

At first you have to delete records from Permission table with following script-

DELETE FROM Permissions
WHERE MemberID IN 
(
    SELECT MemberID FROM Members
    WHERE UserID = 7
)

In second step, you have to delete records from Members table as below-

DELEET FROM Member WHERE UserID = 7

In third step, you have to delete users from Users table as below-

DELETE from Users WHERE UserID = 7

Delete rows from multiple tables due to data from one table

1 Answers1