CODEWITHSUNDEEP
mysql
Tim van Dalen
Tim van Dalen

Reputation: 1471

Removing all records from a table that don't exist in another table

I've got a table with a lot (>20.000) records. It's time to clean those up a little because queries are beginnen to take up a lot of recourses. I have another (temporary) table with a recent records. All records in the first table that don't exist in the second one can go. However, I need to keep the id's for the recent records because of some joins, so I can't just TRUNCATE and then copy the table. Is there any way yo do this?

Upvotes: 8

Views: 3762

Answers (2)

Alex
Alex

Reputation: 14618

DELETE FROM old_table WHERE id NOT IN (SELECT id FROM temp_table)

Edit:
id NOT IN (SELECT id FROM temp_table) is a lousy construction. If you have large tables and your server settings are tuned to low memory, you will execute this forever.

@Piskvor's answer is a longer, but a much better query, that will work best on low-memory setups. And when I say "low memory", I mean database servers that do not take up a full high-end computer, so pretty much any system that manages a small business website or stuff like that.

Upvotes: 14

Piskvor left the building
Piskvor left the building

Reputation: 92752

This will give you the ids of rows that don't have a corresponding row (matched by recordid column - yours may be different) in the other table:

SELECT t1.id 
    FROM firsttable t1
    LEFT JOIN secondtable t2
WHERE t1.recordid = t2.recordid
    AND t2.id IS NULL

That allows you to check that you're actually selecting the correct rows to delete. Then, plug it into a DELETE:

DELETE FROM firsttable WHERE firsttable.id IN (
    SELECT t1.id 
        FROM firsttable t1
        LEFT JOIN secondtable t2
    WHERE t1.recordid = t2.recordid
        AND t2.id IS NULL
)

Upvotes: 4

Related Questions