mysql update query with select

Question

This is my mysql query, but it is not working

update  jos_users set name =
(SELECT name 
FROM jos_users
WHERE id = 478) where id =477

can any one please tell how to execute this query? or other possibility?

see : http://stackoverflow.com/questions/2334712/sql-server-update-from-select — xkeshav, Jul 31 '12 at 05:13

score 1 · Accepted Answer · answered Jul 31 '12 at 05:16

the error-message you should get is:

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

wich means you cant use the same table you're updating in a subselect. anywa, there's a litte workaround to avoid this: just use a nested subselect instead:

update
  jos_users
set
  name = (select name from
            (SELECT name FROM jos_users WHERE id = 478)
               AS subselect_value)
where
  id = 477

mysql update query with select

1 Answers1