CODEWITHSUNDEEP
mysql
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

How can I delete rows from a table based on duplicates in once column but differences in another?

I have some data pollution in a table that I'm having a hard time removing... I've fixed the cause of the pollution so it won't happen again, but there are a bunch of records I now need to delete and I'm having a hard time figuring out how.

The nature of the problem is as follows.

I have a table that has userIds and groupIds. It is valid for a user to exist in multiple groups, so this isn't something I can solve with a unique association.

In my case, let's say groups 4 and 5 are mutually exclusive (meaning a user can't belong to both).

I need to run a delete on the user_groups table where I can say (in english): Delete this row if the userID = X and the group ID = 4 IF there is also a row where the userID = X and the groupID = 5.

I don't want to delete ALL the rows where the groupID = 4, only those that apply to users who also have a row containing groupID = 5.

All help greatly appreciated.

Upvotes: 0

Views: 52

Answers (1)

Holger Brandt
Holger Brandt

Reputation: 4354

This will delete all the users in group 4 that have a duplicate in group 5.

DELETE FROM user_groups AS G1 
WHERE G1.groupID=4 
  AND G1.userID IN (SELECT G2.userID FROM user_groups 
                    WHERE G2.userID=G1.userID AND G2.GroupID=5)

Upvotes: 1

Related Questions