How to delete records in DB with mySQL using group

Question

This question already has answers here:

Closed 9 years ago.

Possible Duplicate:
SQL Delete: can't specify target table for update in FROM clause

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?

Answer 1

  $result=mysql_query("select CourseName from TAB group by CourseName having count(CourseName) < 100");
while($row=mysql_fetch_array($result)){
mysql_query("delete from tab where courses='$row[0]'");
}

Answer 2

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

• MySQL Error 1093 - Can't specify target table for update in FROM clause

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
                        )

Answer 3

One of the easiest ways is to create a temporary table from your first query, and then in a second statement delete all the courses not present in the temporary table.

Answer 4

Perhaps this may work?

DELETE FROM tab AS a 
INNER JOIN (select CourseName from TAB group by CourseName having count(Coursename)>100) as b
ON a.CourseName = b.coursename