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)
