Update Custom Post Type Taxonomies with SQL

Why am I getting this error?

Because the table wp_term_taxonomy does not have the column object_id.

And the correct table that you should update is the wp_term_relationships table which does have the column object_id. See the WordPress database diagram and also the WordPress database description for more details.

So your SQL query should be:

UPDATE wp_term_relationships SET term_taxonomy_id = $cat_id WHERE object_id = $post_id;

And it could work, but then:

First, the query would affect all rows where the object_id is the value of the $post_id. E.g. When $cat_id = 1 and $post_id = 2:

|------------------------------|------------------------------|
|         Before Update        |         After Update         |
|------------------|-----------|------------------|-----------|
| term_taxonomy_id | object_id | term_taxonomy_id | object_id |
|------------------|-----------|------------------|-----------|
| 10               | 2         | 1                | 2         |
| 11               | 2         | 1                | 2         |
| 35               | 2         | 1                | 2         |

Secondly, you would also need to update the count column in the wp_term_taxonomy table for rows where term_taxonomy_id is in the above list (10, 11, 35 and 1) — e.g. count - 1 for 10, 11 and 35 (i.e. the old categories).

A Better Solution

Instead of messing with custom SQL, you should just use the WordPress term/category APIs like wp_set_post_categories() or wp_set_post_terms() which you can use to easily update the post categories — the later function should be used with custom taxonomies. So:

wp_set_post_categories( $post_id, $cat_id );       // Set $cat_id as the only category
wp_set_post_categories( $post_id, $cat_id, true ); // Or add $cat_id to the existing list

Additional Notes

• You really should escape dynamic data being used in SQL queries; however, I’m just going to assume you’ve already sanitized both the $cat_id and $post_id values? (e.g. $cat_id = absint( $_POST['cat_id'] ))