Using aggregate function in WHERE clause to UPDATE a table

Question

I have two tables employee and department. They are linked with dep_id column, which is a primary key in departments and a foreign key in employee.

My goal is to:

Update salaries of all employees whos salaries are above average salary in their department. Right now I have 2 queries to do this:

UPDATE employee E 
SET E.payroll = E.payroll + 1000
WHERE E.payroll > (SELECT AVG(E2.payroll) FROM employee E2 WHERE E.dep_id = E2.dep_id)

I get an error: #1093 - Table 'E' is specified twice, both as a target for 'UPDATE' and as a separate source for data

2) In department table I store the amount of employees per department and a total payroll they have per department

UPDATE employee E 
SET E.payroll = E.payroll + 1000
WHERE E.payroll > (SELECT department.dep_payroll / department.dep_amount FROM department  WHERE department.dep_id = E.dep_id)

This one works fine, but because I have a trigger which fires after\before (I checked both ways) updating employee table and updates dep_payroll it blocks me from executing 2nd query giving this error #1442 - Can't update table 'department' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Here it is

CREATE TRIGGER `t3` BEFORE UPDATE ON `employee`
 FOR EACH ROW UPDATE department
SET dep_payroll = dep_payroll - OLD.payroll + NEW.payroll
WHERE dep_id = NEW.dep_id

How can I execute an update query?

Possible duplicate of [How to UPDATE from a SELECT in SQL Server?](http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server) The rules used to update MySQL database is the same, as it's standard T-SQL language. — Maciej Los, May 11 '17 at 13:34
@MaciejLos, I didn't see that one. But what about second query? — Le garcon, May 11 '17 at 13:40
IIRC this is a flaw in MySQL. You cannot use the same table that you are updating in the where clause. However, it usually works when you get one level deeper; something along the lines of `WHERE E.payroll > (SELECT * FROM (SELECT AVG(E2.payroll) FROM employee E2 WHERE E.dep_id = E2.dep_id) x)`. — Thorsten Kettner, May 11 '17 at 13:54
@ThorstenKettner, it's magical, thanks! I'll post it in the answer — Le garcon, May 11 '17 at 14:04

Andomar · Answer 1 · 2017-05-11T13:48:40.163

Consider a temporary table to store the list of employees to update. Then run the update itself in a separate query:

create temporary table list (emp_id int)
;
insert  list
select  emp_id
from    YourTable emp
join    (
        select  dep_id
        ,       avg(payroll) as avg_pay
        from    YourTable
        group by
                dep_id
        ) dep
on      emp.dep_id = dep.dep_id
where   payroll > avg_pay
;
update  YourTable
set     payroll = payroll + 1000
where   emp_id in (select emp_id from list)
;

Example at regtester.

score 0 · Answer 2 · answered May 11 '17 at 14:10

0

I don't know whether it's the most effective way to write such query, buy thanks to @ThorstenKettner I've managed to find the answer

UPDATE employee E 
SET E.payroll = E.payroll + 1000
WHERE E.payroll >
(SELECT * FROM 
 (SELECT AVG(E2.payroll) FROM employee E2 INNER JOIN employee E ON E.dep_id = E2.dep_id) 
 X)

However, it still leaves the second part of my question unanswered.. Is it a flaw in MySQL or a trigger written wrongly?

answered May 11 '17 at 14:10

Le garcon

7,197
9
31
46

Not sure why you have an `inner join` in the subquery, that would count a lot of employees multiple times. You're also not looking at the average salary for the department where the employee works: there's no link between the subquery and the row you're updating. – Andomar May 11 '17 at 14:16
@Andomar, well, I was testing it on my sample database and it works – Le garcon May 11 '17 at 14:23

Using aggregate function in WHERE clause to UPDATE a table

2 Answers2