Welcome to WebmasterWorld Guest from 50.19.0.90

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)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:222
votes: 0


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)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:222
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 0


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
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members