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

Question

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

Answer 1

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.