Here is a working example of how to do an UPDATE JOIN with MySQL:
###MySql UPDATE LEFT / CENTER / RIGHT JOIN Example###
This is the correct way to update a table when using a `JOIN`. They key is that the `SET` needs to come after the `JOIN`.
UPDATE wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
SET wp_term_taxonomy.taxonomy = ‘category’
WHERE 1
AND wp_term_taxonomy.taxonomy = ‘category’
AND wp_term_taxonomy.term_id IN (1)
This WILL NOT WORK because the `SET` is in the wrong place:
UPDATE wp_posts
SET wp_term_taxonomy.taxonomy = ‘category’
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1
AND wp_term_taxonomy.taxonomy = ‘category’
AND wp_term_taxonomy.term_id IN (1)
