Mysql for each row check to use if a field is in another table and if not delete that row

I have two tables, first one is called ‘users’ and the second is called ‘profiles’. Both tables have a field called ‘id’, the profiles table also has a field called ‘user_id’ which I use to join the two tables. Long story short when users are deleted their profiles are not (that problem has been fixed), I would like to find all the profile in the profile table that don’t have a user connected to them. I need to somehow check each row in the profile table to see if the ‘user_id’ field exists in the ‘users’ table under the field ‘id’ and if it does not delete the row in the profile table that contains that ‘user_id’.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can use not exists:

select p.*
from profiles p
where not exists (select 1 from users u where p.profile_id = u.id);

If you want to delete the rows:

delete p from profiles p
where not exists (select 1 from users u where p.profile_id = u.id);


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x