Tag Archives: example

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)

Posted in Tech Tips, Web Development | Tagged , , , , | Leave a comment