mysql delete group,如何使用groupby在MySQL中删除数据库中的记录

I have one table only (call this table TAB), representing University exams. I have the following attributes: CourseName, CourseCode and year. I want to delete all courses that have a cardinality less than 100. If I type

select CourseName from TAB group by CourseName having count(CourseName) < 100;

I have an exact result. But if I want to delete this entries I try with

delete from TAB where CourseName not in (select CourseName from TAB group by CourseName having count(CourseName) > 100);

but the system returns an error:

Error Code: 1093 You can't specify target table 'TAB' for update in FROM clause

How I have to delete these records?

解决方案

Please see the answer at the following link. It will solve your issue:

Basically, you can't delete from (modify) the same table you use in the SELECT. There are ways around it documented at that page.

The following will work by making your nested select a temp table.

delete from TAB

where CourseName not in (select temp.CourseName

from (select t.CourseName

from TAB t

group by t.CourseName

having count(t.CourseName) > 100

) as temp

)