Home | About Chris Hope | Contact Chris Hope | Search:

MySQL: Delete records in one table that are not in another

Posted May 13th, 2009 in MySql

Last week I looked at how to find records in a table with MySQL that are not in another table and this week look at how to delete records in a table with MySQL that are not in another table.

Example tables

The examples below are the same as in last week's post and use three tables as follows:

content: contains the content pages for a website. The primary key is content_id.

tags: the "tags" that a page is tagged with. The primary key is tag_id.

content_to_tags: a table that creates a many-to-many relationship between the above two tables; a page can belong to multiple tags.

Example 1

The first example deletes records in content_to_tags that have no associated record in content. This could have happened if the application deleted a record from content but didn't delete the associated records from content_to_tags.

DELETE FROM content_to_tags
WHERE NOT EXISTS (
    SELECT *
    FROM content
    WHERE content_id = content_to_tags.content_id
)

In a properly ACID compliant database with foreign key constraints there shouldn't be any records in content_to_tags that aren't in content but if you haven't set up the constraints (when using INNODB) or are using MyISAM tables then it's quite possible for this to have happened.

Example 2

The second example deletes records in content_to_tags that have no associated record in tags. This could have happened if the application deleted a record from tags but didn't delete the associated records from content_to_tags. The same note about foreign key constraints applies.

DELETE FROM content_to_tags
WHERE NOT EXISTS (
    SELECT *
    FROM tags
    WHERE tag_id = content_to_tags.tag_id
)

Table aliasing

Note that you can't use table aliasing and must use the full table name in the NOT EXISTS part of the query. The following example won't work (the aliased table name is in red):

DELETE FROM content_to_tags ctt
WHERE NOT EXISTS (
    SELECT *
    FROM tags
    WHERE tag_id = ctt.tag_id
)

This will result in the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ctt
WHERE NOT EXISTS (
SELECT *
FROM tags
WHERE tag_id = ctt.tag_id
)' at line 1

You probably wouldn't have done this yourself, but I did when I was testing out the queries for this article and got that error so thought it best to share :)

Comments

blog comments powered by Disqus