MySQL删除子查询的所有结果 [英] MySQL delete all results of sub-query

问题描述

好吧，如果你想要一个后台的故事，看看我的以前的问题

计算出哪些记录不重复是很容易的：

  SELECT * 
 FROM eventlog 
 GROUP BY event_date，user 
 HAVING COUNT（*）= 1 
 ORDER BY event_date，user 
  

这将返回我所有的不重复的。所以我想我会把它们移动到另一个名为no_duplicates的表，然后从原表中删除它们。然后，我可以在原始表中单独看到重复的内容，修复它们，并将no_dupes添加回来。但是，虽然：

  INSERT INTO no_duplicates 
 SELECT * 
 FROM eventlog 
 GROUP BY event_date，用户
 HAVING COUNT（*）= 1 
 ORDER BY event_date，用户
  

作为一个魅力，以下是一个错误：

  DELETE 
 FROM eventlog 
 GROUP BY event_date，用户
 HAVING COUNT（*）= 1 
 ORDER BY event_date，用户
  

我的猜测是，当查询返回唯一的，已经在表中的记录时，由聚合函数删除不是犹太人。这是可以理解的，除了我不知道还能做什么来确保只有我移动的记录被删除。我搜索并发现没有INSERT杀死原始表中的记录语法，我的猜测是，它将失败，由于同样的原因删除失败。

所以，有人可以帮我找到缺少的一块吗？

解决方案

首先，你不需要INSERT和ORDER BY删除。如果您需要它们进行演示，那么这只是有用的，而不是这些操作。

如果您的EVENTLOG表具有主键（如ID），则可以形成DELETE - 这样的说明：

  DELETE 
 FROM eventlog 
 WHERE id IN（
 SELECT * 
 FROM eventlog 
 GROUP BY event_date，user 
 HAVING COUNT（*）= 1 
）
  

Okay, so if you would like a back story, look at my previous question

Figuring out which of my records is not a duplicate is quite easy:

SELECT *
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

This returns all of my non-duplicates. So I thought I'd move them over to another table called "no_duplicates" and then delete them from the original table. Then I could see the duplicates all alone in the original table, fix them up, and add the no_dupes back. But while:

INSERT INTO no_duplicates
SELECT *
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

Works like a charm, the following throws an error:

DELETE
FROM eventlog
GROUP BY event_date, user
HAVING COUNT(*) = 1
ORDER BY event_date, user

My guess is that while the query returns unique, already-in-the-table records, deleting by a aggregate function isn't kosher. Which is understandable except I don't know what else I can do to secure that only the records I moved are deleted. I searched and found no "After INSERT kill the records in the original table" syntax, and my guess is that it would fail anyway, for the same reason the delete failed.

So, can someone help me find the missing piece?

解决方案

Firstly, you do not need the ORDER BY on the INSERT and DELETE. It's just useful if you need them for presentation which is not the case with those operations.

If your EVENTLOG table has a primary key (like ID) you could form your DELETE-statement like this:

DELETE 
FROM eventlog
WHERE id IN (
  SELECT *
  FROM eventlog
  GROUP BY event_date, user
  HAVING COUNT(*) = 1
)

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