删除所有重复的主题，几乎没有条件 [英] DELETE all duplicate topics with few conditions

问题描述

• 必须删除重复的副本相同的 object_id


• 必须只保留最新的记录（最大的 topic_id ）（topic_id是每个主题AI的唯一ID） / li>
  

到目前为止，我已经做了（用select ...测试）

  SELECT topic_id，object_id，title，url，date 
 FROM topic GROUP BY title 
 HAVING（COUNT（title）> 1）
 ORDER BY topic_id DESC 
  

但不符合条件。

我正在使用mysql。

解决方案

在 MySQL 中，不能指定目标表一个子查询中的 DML 操作（除非你将其嵌套在一个级别以上，但在这种情况下，您将无法获得可靠的结果，并且不能使用相关的子查询） p>

U se a JOIN ：

  DELETE td 
 FROM topic td 
 JOIN主题ti 
 ON ti.object_id = td.object_id 
 AND ti.title = td.title 
 AND ti.topic_id> td.topic_id; 
  

在主题（object_id，title，topic_id）上创建一个索引为此工作快速。

I'm trying to make sql who will delete all duplicate titles BUT must delete duplicates with these conditions:

• must delete only duplicates with same object_id
• must keep only the newest record (biggest topic_id) (topic_id is the unique id for every topic AI)

So far I've done that (testing with select...)

SELECT topic_id,object_id,title,url,date 
FROM topics GROUP BY title 
HAVING ( COUNT(title) > 1) 
ORDER BY topic_id DESC

But doesn't meet the conditions.
I'm using mysql.

解决方案

In MySQL, you cannot specify the target table to a DML operation in a subquery (unless you nest it more than one level deep, but in this case you won't get reliable results and cannot use correlated subqueries).

Use a JOIN:

DELETE  td
FROM    topics td
JOIN    topics ti
ON      ti.object_id = td.object_id
        AND ti.title = td.title
        AND ti.topic_id > td.topic_id;

Create an index on topics (object_id, title, topic_id) for this to work fast.

这篇关于删除所有重复的主题，几乎没有条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！