CODEWITHSUNDEEP
mysqlsql
bart2puck
bart2puck

Reputation: 2522

delete from multiple tables

I have a table of users user id

3 ,Frank
4 ,Steve
5 ,Joe

and a table of roles where column 1 is id of users in above table:

1, billing
3, Admin
2, Admin
4, user
5, billing

as you can see users 1 and 2 has been orphaned, and there is no user with id of 2. how can i delete this entry from the roles table? there could be dozens of entries in roles that dont have a user.

will this work?

 DELETE from roles,users where roles.userId!==users.userId

Upvotes: 0

Views: 56

Answers (3)

spencer7593
spencer7593

Reputation: 108510

Q: Will this work?

A: Close, but no cigar. No, the statement in the question won't perform the specified operation.

First, write a query to identify the orphaned rows. An anti-join pattern is a common approach (though there are several other workable approaches):

SELECT r.*
  FROM `roles` r
  LEFT
  JOIN `user` u 
    ON u.id = r.user_id
 WHERE u.id IS NULL

This uses an "outer" join, to return all rows from roles, along with matching rows from user. If no matching row is found in user, the columns from the user table will be returned as NULL. Given the equality comparison in the join predicate (ON clause), we know that all matching rows will have a non-NULL value for u.id. The "trick" is the predicate in the WHERE clause that excludes all rows that found a match, so we are left with rows in roles that don't have a matching row in users

(If you want to examine the rows that will be deleted and/or create a "backup" of those rows, this query gives you that.)

Next, convert the SELECT statement into a DELETE statement. For this particular statement, it's very straightforward with MySQL. The only change required is to replace the SELECT keyword with the DELETE keyword:

DELETE r.*
  FROM `roles` r
  LEFT
  JOIN `user` u 
    ON u.id = r.user_id
 WHERE u.id IS NULL

This is an example of just one of several workable approaches.

Upvotes: 0

Karoly Horvath
Karoly Horvath

Reputation: 96326

DELETE FROM roles WHERE id NOT IN (SELECT id FROM users)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You don't need to delete from users if the user is not there. So you can just do:

delete r from roles r
    where not exists (select 1 from users u where u.userId = r.userId);

As mentioned in a comment, if you declared the roles.userId value to be a foreign key reference, then the database would not let this happen. After the data is fixed, you can do:

alter table roles add constraint foreign key (userId) references user(userId);

Upvotes: 1

Related Questions