MySQL 使用 max 函数通过子查询更新查询

Question

谁能看出下面的查询有什么问题吗？

update sub set name='dd' where salary=(select max(salary) from sub);

执行后发现错误并且 MariaDB 表示，

SQL query: Documentation

update sub set name='dd' where salary=(select max(salary) from sub)

MySQL said: Documentation

\ #1093 - Table 'sub' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Answer 1

MySQL 不允许这种语法。您可以使用 join 来代替:

update sub s
inner join (select max(salary) salary from sub) m on m.salary = s.salary
set s.name = 'dd'