How to delete records in DB with mySQL using group by

Question

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?

score 3 · Accepted Answer · edited May 23 '17 at 10:30

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
                        )

score 0 · Answer 2 · answered Jul 20 '11 at 15:20

0

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.

answered Jul 20 '11 at 15:20

Ken

613
4
6

Ok, how can I create a temp table? – Baduel Jul 20 '11 at 15:38

score 0 · Answer 3 · answered Jul 20 '11 at 15:24

0

  $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]'");
}

answered Jul 20 '11 at 15:24

Rasel

15,499
6
40
50

Deleting results individually uses more bandwidth and processing power on the SQL server's end. It requires downloading a list of maybe hundreds of thousands of rows that the web server does not need and then execute one hundred thousand additional SQL statements. – Devin Burke Jul 20 '11 at 15:29

score 0 · Answer 4 · answered Jul 20 '11 at 15:34

0

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

answered Jul 20 '11 at 15:34

Kevin Burton

11,676
2
24
37

How to delete records in DB with mySQL using group by

4 Answers4

Linked