MySQL使用Have和Count进行子查询删除 [英] MySQL DELETE With a Sub-Query using Having and Count

问题描述

我正尝试使用以下查询删除几个条目:

Am trying to DELETE several entries using the following Query:

首先，我找到要使用此查询删除的条目:

First i find the entries that i want to delete using this query:

SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1);

然后我将此查询添加到DELETE语句:

Then i add this query to the DELETE statement:

DELETE FROM account WHERE guid IN (SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1);

但是我得到这个错误:

您无法在FROM子句中指定要更新的目标表帐户"

推荐答案

我认为您需要使用临时表来满足您的需求，如下所示:

I think you need to use temporary table to achieve your need as below:

1. 第1步:创建临时表

1. Step1: Create temp table

CREATE TEMPORARY TABLE MyTemp
SELECT guid FROM account 
GROUP BY guid,type HAVING count(type) > 1;

在您的delete语句中使用临时表

Use the temp table in your delete statement

DELETE FROM account 
WHERE guid IN (SELECT guid FROM MyTemp);

删除临时表

Drop the temp table

DROP TEMPORARY TABLE MyTemp;

我认为处理* 两个嵌套表也可以:

I think a work around with *two nested tables also works:

  DELETE FROM account 
    WHERE guid IN 
     (SELECT guid FROM 
       (SELECT guid FROM account 
       GROUP BY guid,type HAVING count(type) > 1) as MyTemp
    )

这篇关于MySQL使用Have和Count进行子查询删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！