用从同一表计算的AVG更新MySQL表

Question

我正在尝试update所有薪资低于表中所有薪资平均值的行。

UPDATE PostOfficeStaff 
SET salary = salary * 1.05 
WHERE officeNo = 1 
AND salary < (SELECT AVG(salary) FROM PostOfficeStaff)

这让我说错了

不能指定目标表“PostOfficeStaff”以便在FROM子句中进行更新

我也在没有FROM PostOfficeStaff的情况下尝试过这个查询，虽然它没有给出错误，但它也什么也不做。

我该怎么做？

Answer 1

可以将其包装在外部查询中，如下所示：

UPDATE PostOfficeStaff 
SET salary = salary * 1.05 
WHERE officeNo = 1 
AND salary < (SELECT * FROM  (
                              SELECT AVG(salary) 
                              FROM PostOfficeStaff
                             ) as t1
             )

检查：https://dev.mysql.com/doc/refman/8.0/en/update.html

来自MySQL文档：

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

相反，您可以使用多表更新，其中将子查询移到要更新的表列表中，使用别名在最外层WHERE子句中引用它，如下所示：

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

Answer 2

您可以在计算表中使用JOIN，如：

UPDATE PostOfficeStaff 
JOIN (
    SELECT AVG(salary) avg_salary FROM PostOfficeStaff
) avg_salary ON avg_salary > salary
SET salary = salary * 1.05 
WHERE officeNo = 1;

SQL连接小提琴