How to use data in same table on UPDATE query?

Question

update accounts set password=(select password from accounts where name='joongsu') 
where id=(select accountid from characters where name='Nobless')

it doesn't work with error message "You can't specify target table 'accounts' for update in FROM clause"

Why doesn't it work? select queries in above only return 1 row.

You query says UPDATE... password=password WHERE... – Mihai Mar 29 '14 at 11:49 — Mihai, Mar 29 '14 at 11:49

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Perhaps you should try this one:

UPDATE accounts
SET accounts.password =
(
    SELECT something.password
    FROM (SELECT * FROM accounts) AS something
    WHERE something.name='joongsu'
)
WHERE accounts.id=(SELECT accountid FROM characters WHERE name='Nobless');

It's a hack, but I tested it and it works on my test data. For some reason MySQL doesn't allow using the same table in inner queries as the one being updated.

score 1 · Answer 2 · answered Mar 29 '14 at 12:00

UPDATE
    accounts AS account_to_be_updated
  JOIN 
    characters
      ON  characters.accountid = account_to_be_updated.id
      AND characters.name = 'Nobless'
  CROSS JOIN
    ( SELECT password
      FROM   accounts 
      WHERE  name = 'joongsu'
    ) AS existing_account
SET 
    account_to_be_updated.password = existing_account.password ;

AK47 · Answer 3 · 2014-03-29T11:17:24.110

0

Is this what you looking out for?

;with CTE as 
(
select password from accounts  where name='joongsu' limit 1
)
update accounts set password= CTE.password
    where id in 
(select accountid from characters where name='Nobless')

edited Mar 29 '14 at 11:17

answered Mar 29 '14 at 10:44

AK47

3,577
3
15
33

I want to change password of ID whose character name is "Noblesse" to ID "joongsu"(not nickname)'s password. – Ux5 Mar 29 '14 at 10:48
I'm sorry but what i want is not 'joongsu', but joongsu's password. (select password from accounts where name='joongsu') – Ux5 Mar 29 '14 at 10:54
it returns this message "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 '1 password from accounts where name='joongsu') where id in (select accountid fro' at line 1". is it because my mysql version is not latest? – Ux5 Mar 29 '14 at 11:03
not sure friend, code which i have given is of SQL Server. Wait will try to get code of mysql.till that time, pls try again bu updated code. – AK47 Mar 29 '14 at 11:05
it returns "You can't specify target table 'accounts' for update in FROM clause". I think the reason is that in subquery, we designated 'acconts' table which is already used in main query. – Ux5 Mar 29 '14 at 11:11
MySQL does not support (writeable) common table expressions (and it would need the statement terminator `;` at the end of the statement not at the beginning. – a_horse_with_no_name Mar 29 '14 at 11:41
Oh! then sorry friend, I can't help you now. Will try out some code on mysql also in coming future. – AK47 Mar 29 '14 at 11:54

How to use data in same table on UPDATE query?

3 Answers3