Welcome to WebmasterWorld Guest from 54.197.94.141

Forum Moderators: open

Message Too Old, No Replies

Copying columns from one table to another in MySQL

   
1:49 pm on Apr 4, 2012 (gmt 0)

5+ Year Member Top Contributors Of The Month



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?
1:53 pm on Apr 4, 2012 (gmt 0)

5+ Year Member Top Contributors Of The Month



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`;
10:49 pm on Apr 4, 2012 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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