homepage Welcome to WebmasterWorld Guest from 50.16.112.199
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, Moderator: open

Databases Forum

    
MySQL - Update row 1 column to row 2 value
migthegreek

5+ Year Member



 
Msg#: 4218855 posted 2:11 pm on Oct 19, 2010 (gmt 0)

I need to update one column value in a specific row with the value of the same column in a different row.

I don't know the syntax for this, but my brain is thinking something like this (just to show what I need):

UPDATE temp_users SET notes = (SELECT notes FROM temp_users WHERE id = 1) WHERE id = 2

 

brotherhood of LAN

WebmasterWorld Administrator brotherhood_of_lan us a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



 
Msg#: 4218855 posted 5:05 pm on Oct 19, 2010 (gmt 0)

Did you try it and it worked migthegreek?

Check out subqueries [dev.mysql.com] for more info.

migthegreek

5+ Year Member



 
Msg#: 4218855 posted 9:08 am on Oct 20, 2010 (gmt 0)

Yes, and it doesn't work.

"You can't specify target table 'temp_users' for update in FROM clause "

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 10:14 am on Oct 20, 2010 (gmt 0)

how about

UPDATE temp_users SET notes = (SELECT a.notes FROM temp_users a WHERE id = 1) WHERE id = 2

migthegreek

5+ Year Member



 
Msg#: 4218855 posted 10:45 am on Oct 20, 2010 (gmt 0)

Still get MySQL error:

#1093 - You can't specify target table 'temp_users' for update in FROM clause

I think I already tried using table aliases.

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 10:59 am on Oct 20, 2010 (gmt 0)

in which case try something like this.

DECLARE myvariable VARCHAR(100); -- whatever the notes field is
SET myvariable = (SELECT notes FROM temp_users WHERE id = 1);
UPDATE temp_users SET notes = myvariable WHERE id = 2;

is this a one off thing? or something you'll need to do often?

migthegreek

5+ Year Member



 
Msg#: 4218855 posted 11:14 am on Oct 20, 2010 (gmt 0)

Something I will need to do often. But I still get an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE myvariable VARCHAR(100)' at line 1

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 11:37 am on Oct 20, 2010 (gmt 0)

did you delete the comment?
the sequence i suggested should work, maybe you need to make it all a stored procedure? and then call the stored procedure from your php instead of sending a select statement.

i admit i only use stored procedures for all kinds of reasons, so i'm unsure of the syntax to send multiple queries to mySQL from php

are you doing this from a php page or from another tool directly interacting with the database?

alternatively the obvious workaround is within the php code, query the database to get the required notes then query the database again to update the appropriate field.

migthegreek

5+ Year Member



 
Msg#: 4218855 posted 11:44 am on Oct 20, 2010 (gmt 0)

Yes, sorry - I will explain a bit more. This query needs to be run in phpMyAdmin, although I have not used variables before. It is for a colleague of mine, to run this query occasionally but if all else fails I will simply build him a PHP page. I was hoping there is a quick way using a direct SQL statement.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 4:33 pm on Oct 20, 2010 (gmt 0)

Currently, you cannot update a table and select from the same table in a subquery.


From the manual [dev.mysql.com]

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 9:41 pm on Oct 21, 2010 (gmt 0)

are you trying to do this for the whole table? eg possibly 100's or 1000's of rows at teh same time?
or are you trying to do it for one or two KNOWN rows ( eg you know the id or id's) that you will want to update?

enigma1

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4218855 posted 10:09 am on Oct 22, 2010 (gmt 0)

Use couple of aliases for the same table you don't need a select.

UPDATE temp_users tu, temp_users ts SET tu.notes = ts.notes where tu.id=1 and ts.id=2

topr8

WebmasterWorld Senior Member topr8 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4218855 posted 11:49 am on Oct 22, 2010 (gmt 0)

excellent solution enigma1, i didn't think of that at all

migthegreek

5+ Year Member



 
Msg#: 4218855 posted 9:13 am on Oct 25, 2010 (gmt 0)

Works perfectly, thank you. Never would have thought of that myself.

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