Copying columns from one table to another in MySQL

[ocon]

I'm trying to copy several columns from one table to another in MySQL where the ids match. (From 'temp' to 'places')

I'm using the following code:

UPDATE `places` SET `places.wc` = `temp.gen_wc`, `places.cc` = `temp.gen_cc`, `places.gh` = `temp.oth_gh` WHERE `places.id` = `temp.id`;

I get a "Unknown column 'temp.id' in 'where clause'" error even though there is an "id" column in the "temp" table. If I take the tick marks away from that, then I'll get a similar error on another column that exists.

Is there something fundamentally wrong with my statement?

[ocon]

I've also tried:

UPDATE `places` SET `places.wc` = `temp.gen_wc`, SET `places.cc` = `temp.gen_cc`, SET `places.gh` = `temp.oth_gh` WHERE `places.id` = `temp.id`;

[Dijkgraaf]

Is there something fundamentally wrong with my statement?

Yes there is something fundamentally wrong.
Your temp table isn't actually included in your SQL statement as you don't have a FROM clause that lists it as table to include in the query.
It has to be something like the below (please note I haven't run this so it may still have syntax errors)


UPDATE `places`
SET `places.wc` = `temp.gen_wc`, `places.cc` = `temp.gen_cc`, `places.gh` = `temp.oth_gh`
FROM 'places'
INNER JOIN 'temp' ON `places.id` = `temp.id`;

I did a search on mysql update select from
and also came up with another example

UPDATE related_category
INNER JOIN
product_category
ON related_category.rel_cat_id = product_category.cat_id
SET related_category.rel_cat_name = product_category.cat_name