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:
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:
if you are sure, these are the rows you want to delete, replace SELECT * with DELETE:
Yes, you want to use outer joins.
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.