get value from another row

[asmith]

update tablename set username=name where id="1"

 

the above code put the value of name column in the username column where row is 1 .

how can i put the value of the "name" column which is in row="2" ,  in the username column in row="1" ?

[~n[EO]n~]

That cannot be done I think

[Zane]

UPDATE tablename SET username = (
SELECT name
FROM tablename WHERE id = 2 LIMIT 1) WHERE id = 1;

 

maybe?

[~n[EO]n~]

@zanus : that was near  but didn't work

 

MySQL said:

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

[asmith]

yea close one  !

gave me that error too !

what a shame !!  :-\

[fenway]

You can't update the source table in the same statement... this is known limitation.