Updating MySQL table using AVG calculated from same table

Question

I am trying to update all rows where the salary is less than the average of all salaries in the table.

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

It is giving me an error saying

You can't specify target table 'PostOfficeStaff' for update in FROM clause

I have tried this query without the FROM PostOfficeStaff as well and while it doesn't give me an error, it also does nothing.

How can I do this?

Answer 1

You can wrap it in an outer query like :

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

Check: https://dev.mysql.com/doc/refman/8.0/en/update.html

From the MySQL docs:

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

Instead, you can employ a multi-table update in which the subquery is moved into the list of tables to be updated, using an alias to reference it in the outermost WHERE clause, like this:

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

You can use JOIN with calculated table like:

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 Join fiddle