如何使用group by在mySQL中删除DB中的记录 [英] How to delete records in DB with mySQL using group by

问题描述

可能重复:
SQL删除:无法为以下对象指定目标表在FROM子句中更新

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

我只有一张表(称为表TAB)，代表大学考试.我具有以下属性:CourseName，CourseCode和year.我要删除基数小于100的所有课程.如果输入

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);

但系统返回错误:

错误代码:1093您不能在FROM子句中指定要更新的目标表'TAB'

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:

• MySQL错误1093-无法在FROM子句中指定要更新的目标表

基本上，您不能从(修改)与SELECT中使用的表相同的表中删除.该页面上记录了许多解决方法.

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

以下操作将使嵌套的select成为临时表.

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
                        )

这篇关于如何使用group by在mySQL中删除DB中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！