如何在相同的表中使用更新查询的数据?

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"

它不使用错误消息"你不能指定目标表'帐户'来更新FROM子句"

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

为什么它不工作?选择上面的查询只返回一行。

3 个解决方案

#1

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.

它是一个hack，但是我测试了它，它对我的测试数据起作用。出于某种原因，MySQL不允许在内部查询中使用与正在更新的表相同的表。

#2

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 ;

#3

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')

#1