Eugene Cuz Eugene Cuz - 1 year ago 76
MySQL Question

how to delete from one table where count less than?

I have 2 tables.

I use this query to find the number of occurences of a field from one table in another table

select, t.tag_text, count(*) cnt from sms s, template as t
group by t.tag_text
order by cnt desc

How can I delete all rows from template if they occure in sms less than 5 times for example? Which would mean if count is less than 5. I use MySQL.

Answer Source
delete from template 

where   id in
            select      id_template
            from        sms
            group by    id_template
            having      count(*) < 5