Is is possible to rebuild wp_term_relationships table?

Question

Stupidly, I truncated my posts table with out thinking about the term relationships. Now I have a bunch of 404s because there are broken term relationships. Is is possible to rebuild the table with the correct pairings?

Answer 1

If your AUTO_INCREMENT value wasn't reset and your new posts will still have unique IDs and you can do:

SELECT tr.*, p.ID
FROM wp_term_relationships AS tr
    LEFT JOIN wp_posts AS p ON tr.object_id = p.ID
WHERE p.ID is NULL;

to see which term relationships are orphans and use a DELETE FROM to fix it.

Answer 2

warning: Truncating your wp_term_relationships deletes everything in that table! if you want to only delete tag term relationships then make sure you use the correct sql.

After truncating the wp_term_relationships table I used a custom script to re-tag my posts. You can do something similar like this:

$numposts = wp_count_posts();
$posts = get_posts(array('numberposts'=>(int)$numposts->publish));
foreach($posts as $post){
    $tags = myCustomTaggingFunc($post); //this returns an array of tags
    wp_update_term($post->ID,$tags);
}

pretty simple.