Using an aggregate function in a WHERE clause to UPDATE a table
I have two tables employee and department . They are associated with the dep_id column , which is the primary key across departments and the employee's foreign key .
My goal:
Update the salary of all employees whose salaries exceed the average salary in their department. Now I have 2 requests:
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 the error: # 1093 - Table 'E' is listed twice, both as target for UPDATE and as a separate data source
2) In the department table, I store the number of employees per department and the total salary 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 works well, but since I have a trigger that fires after \ before (I checked both paths) updating the employee table and updating dep_payroll, it blocks me from executing the second query giving this error # 1442 - Cannot update "department table "in a stored function / trigger, because it is already in use by the operator that called that stored function / trigger. Here
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 do I complete an update request?
source to share
Consider a temporary table for storing a list of employees to update. Then run the update itself in a separate request:
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)
;
source to share
I don't know if the most efficient way to write such a query is to buy thanks to @ThorstenKettner. I 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 this a bug in MySQL, or is it a trigger written incorrectly?
source to share