CODEWITHSUNDEEP
mysql
86Stang
86Stang

Reputation: 349

Removing records from one table based on lack of presence in another

I have two tables that contain member data - 'members' and 'member_data'. For some reason (I've inherited this) the member_data table has more records than the members table. Both tables have a member_id column that seems to match up a users info between the two tables.

I would like to remove records from the 'member_data' table if there is no matching member_id found in the 'members' table. Can someone point me in the right direction?

Upvotes: 0

Views: 41

Answers (2)

Alain
Alain

Reputation: 36954

You can try using a NOT IN statement :

DELETE FROM `member_data`
WHERE `member_data`.`member_id`
NOT IN (
   SELECT `members`.`member_id`
   FROM `members`
);

Anyway, you should correct the bug that inserts or not deletes the unexpected rows.

Upvotes: 1

Pankaj
Pankaj

Reputation: 5250

delete from member_data where member_id not in (select member_id from members);

This query will delete all the records from member_data table not present in members table.

Upvotes: 0

Related Questions