How to clean a table after values missing from another table?

Question

I was unsuccessful trying to migrate a bbPress 1.0 instalation to the new bbPress 2.0 plugin on my WordPress, which made quite a mess on my wp_posts table.

I solved that by deleting every posts who was carrying the values topic and reply to the post_type column, and that was good.

But now I noticed that the wp_postmeta seems affected by lots of entries refering to posts that I've deleted. Both tables have the post_id column.

The question is: which SQL command I use to delete the values on wp_postmeta referencing lines who aren't anymore on wp_posts? I know it's some kind of join that I should use but I don't see how it would be to look for something who is not there and delete it.

PS: And what's up with WordPress tables not keeping referential integrity? I'm pretty sure it's possible to delete something automatically from wp_postmeta when deleting something related on wp_posts. Oh well…

Gryphius · Accepted Answer · 2011-06-19T04:21:51.610

a general way to manually delete rows from table a that have no match in table b is:

DELETE FROM table_a WHERE some_id NOT IN (SELECT some_id FROM table_b);

this may not be the most efficient way to do a mass delete (joins could probably do it faster), but I usually prefer a subselect in cases like this, it is easier to understand and therefore reduces the FUBAR probability (you don't want to accidentally delete rows in table_b etc)

so in your case you could probably do something like this:

SELECT * FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

if you are sure, these are the rows you want to delete, replace SELECT * with DELETE:

DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT post_id FROM wp_posts);

Thanks. It worked with a few modifications. Turns out that `wp_posts` have an `id` column instead of an `post_id` one. I added the `low_priority` to the delete command to keep it process friendly. It made an 17MB table a new 500KB one. :) — Alexandre Pereira, Jun 20 '11 at 02:18

score -1 · Answer 2 · answered Jun 19 '11 at 03:35

I know it's some kind of join that I should use but I don't see how it would be to look for something who is not there and delete it.

Yes, you want to use outer joins.

PS: And what's up with WordPress tables not keeping referential integrity?

A lot of newer applications now use entity frameworks and don't explicitly enforce referential integrity, just relationships. Atlassian JIRA, for example, doesn't enforce referential integrity.

How to clean a table after values missing from another table?

2 Answers2