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

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…

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);

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.