homepage Welcome to WebmasterWorld Guest from 54.226.173.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Copying columns from one table to another in MySQL
ocon




msg:4437053
 1:49 pm on Apr 4, 2012 (gmt 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?

 

ocon




msg:4437054
 1:53 pm on Apr 4, 2012 (gmt 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`;

Dijkgraaf




msg:4437276
 10:49 pm on Apr 4, 2012 (gmt 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved