MYSQL Updating multiple fields from fields of another table

Question

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.

Answer 1

you need to do like :

edit

CREATE TABLE newtable SELECT * FROM oldtable;

MySQL creates new columns for all elements in the SELECT. For example: 
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        TYPE=MyISAM SELECT b,c FROM test2;

Answer 2

This should have the effect of updating new1 and new2 in config to the values of new1 and new2 in tmp_config where ever the ids from the two tables match (and null if there is no match in tmp_config).

I believe that's what you said you are trying to do.

From the MySql update reference:

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 12.2.8.1, “JOIN Syntax”. Here is an example:

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

In this case they're not using just the "JOIN" syntax, but the JOIN syntax should still be valid, you just need to do it prior to the SET clause.

It would look something like

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

Answer 3

The multi-table update syntax doesn't allow a JOIN where you put it, see http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE 
  config, tmp_config
SET
  config.new1 = tmp_config.new1,
  config.new2 = tmp_config.new2
WHERE
  tmp_config.tmp_id = config.id

should do the trick (untested, no warranty ;-))

Answer 4

I don't quite understand all of your question. Where have you changed the config? I read your explanations as:

1. You have changed the schema for all applications
2. You have updated the applications' configurations elsewhere

See VolkerK's answer for the correct syntax for multi-table updates.

Which storage engine are you using? If it's InnoDb (or other engine that supports transactions), you should start a transaction before running the query. Then you can verify that the result is the desired one before you commit any changes:

Example:

mysql> START TRANSACTION;

mysql> SELECT * FROM Configs LIMIT 5; -- See what it looks like before

mysql> Run update query here

mysql> SELECT * FROM Configs LIMIT 5; -- Verify that the result is the expected one

mysql> COMMIT;

Answer 5

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!