How to delete rows with a condition that has aggregate calculations with mysql variables-CodePudding

I have a table of persons and I want to delete all of the people that their age is lower the average age. is there a way to hold the average age in a variable and then use it in a query?

I tried this but it didn't work

@age:=(select TIMESTAMPDIFF(YEAR, birthDate, CURDATE()) from Persons);
@avgAge:=(select AVG(@age) AS avg_age FROM Persons);
START TRANSACTION;
delete from Persons 
where ID in (select ID from Persons where @age < @avgAge)
rollback

CodePudding user response：

No need for variables, or even for a CTE. In MySQL, you can do this simply with the delete/join syntax:

delete p
from persons p
inner join (select avg( timestampdiff(year, birthdate, current_date) ) avg_age from persons) a
    on timestampdiff(year, p.birthdate, current_date)  < a.avg_age

The inner join brings the average age of all persons, that we can then use to filter the rows to delete.

CodePudding user response：

You can follow these 3 steps to accomplish what you need:

generate two fields: the age of each of your people, and the overall average age (using a window function with no ordering/partitioning)
join the generated data with your original table on matching ids
delete only those whose age value is less than the average age value

WITH cte AS (
    SELECT ID, 
           TIMESTAMPDIFF(YEAR, birthDate, CURDATE())             AS age
           AVG(TIMESTAMPDIFF(YEAR, birthDate, CURDATE())) OVER() AS avg_age
    FROM Persons
)
DELETE Persons, cte 
FROM       Persons 
INNER JOIN cte 
        ON Persons.ID = cte.ID
WHERE cte.age < cte.avg_age