MySQL UPDATE JOIN

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)

MySql UPDATE JOIN Example

Related Posts:

  • No Related Posts
This entry was posted in Tech Tips, Web Development and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *