MYSQL Updating multiple fields from fields of another table

mysql

Solution

The following has worked for me (USING an INNER join and moving the SET to the end of the query:

UPDATE `config` INNER JOIN `tmp_config` ON (`tmp_config`.`id` = `config`.`id`) 
SET `config`.`new1` = `tmp_config`.`new1`, `config`.`new2` = `tmp_config`.`new2`

Thanks for all your help!

Problem

Scenario: I have an application that has a config table which stores the config data for each website thats uses the application. I have added a couple of extra columns to the config table and rolled this out to all applications. I have since updated these new columns with data that needs to be the same on all the config tables. How would I go about doing this? My first thought would be to duplicate the table and do the following: ``` UPDATE `config` SET `config`.`new1` = `tmp_config`.`new1`, `config`.`new2` = `tmp_config`.`new2` LEFT JOIN `tmp_config` ON (`tmp_config`.`tmp_id` = `config`.`id`) ``` Would this have the desired affect.

Original source