Welcome to WebmasterWorld Guest from 50.16.112.199

Forum Moderators: open

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
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month