Eugene Cuz Eugene Cuz - 1 month ago 16
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.id, t.tag_text, count(*) cnt from sms s, template as t
where s.id_template=t.id
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
delete from template 

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