mySQL: Updating multiple values in a record with a SELECT from another table

Question

Trying to sort out the correct syntax for this UPDATE:

UPDATE `foo` 
   SET (`x`, `y`, `z`) = (SELECT `x`, `y`, `z` 
                            FROM `bar` 
                           WHERE `id` = 'baz');

In the actual query, there are 165 columns so I very much do not want to have to do x = x for each column.

The columns are not a perfect match so SELECT * is not an option.

Matijs · Accepted Answer · 2010-02-18 15:40:04Z

3

In MySQL you can add multiple tables to an UPDATE like this:

UPDATE `foo`, `bar`
SET `foo`.`x` = `bar`.`x`, 
    `foo`.`y` = `bar`.`y`, 
    `foo`.`z` = `bar`.`z`
WHERE `id` = 'baz';

answered Feb 18, 2010 at 15:40

Matijs

2,5531 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jerrygarciuh Over a year ago

Thank you for the reply! I made a test like so: UPDATE test SET test.unique_id = store_import.unique_id, test.synced_on = store_import.synced_on FROM store_import WHERE store_import.entry_id = 137 This gives error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM store_import WHERE store_import.entry_id = 137' at line 4 Can you see where the problem lies?

Matijs Over a year ago

Yeah I already changed the solution since I posted it. I mixed up MySQL and MSSQL. The current version is the one. :-)

jerrygarciuh Over a year ago

Ah well. Thanks. Will take a long time getting all 165 columns into query!

MindStalker Over a year ago

You may want to alias the tables to speed up your input. UPDATE foo a, bar b SET a.x=b.x, a.y=b.y ......

MindStalker · Accepted Answer · 2010-02-18 15:46:16Z

1

You are trying to update items in foo where foo.id = bar.baz?

UPDATE foo JOIN bar
SET foo.x=bar.x, foo.y=bar.y
WHERE foo.id=bar.baz

edited Feb 18, 2010 at 15:46

answered Feb 18, 2010 at 15:40

MindStalker

14.9k3 gold badges29 silver badges20 bronze badges

1 Comment

MindStalker Over a year ago

Note JOIN is the same as , in list of table.

Collectives™ on Stack Overflow

mySQL: Updating multiple values in a record with a SELECT from another table

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related