Question

I m 试图将所有重复标题删除的纸浆厂必须删除与这些条件相重复之处:

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)

到目前为止,我已经这样做(用选择......测试)。

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.

Answer 1

在<代码>MySQL中,你无法具体说明在分局运行的<编码>DML的目标表(除非你在深度上超过一层,但在这种情况下,你获得了可靠的结果,不能使用相关的子序列)。

使用<条码>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;

为快速工作,制作<代码>topics(目标_id,题目:id)索引。

Answer 2

这将删除所有重复的物体_id记录,但主题最突出的记录除外。

delete from topics outer where exists
(
    select 1 from topics inner where 
        outer.object_id = inner.object_id AND 
        inner.topic_id  < outer.topic_id
)

Answer 3

首先,如果你有一天的工作领域,你会更好地确定最新记录的日期。

这项工作将:

SELECT topic_id, object_id, title, url, date 
FROM   topics earlier
WHERE  EXISTS 
    (SELECT newest.topic_id 
     FROM   topics newest 
     WHERE  newest.date      > earlier.date 
     AND    newest.object_id = earlier.object_id)

您重新选择与同一物体相另一行的界线,日期更近。

Answer 4

WITH tbl AS (SELECT topic_id, object_id, row_number() over(partition by object_id order by topic_id DESC) as rnum
FROM topics) DELETE tbl WHERE rnum > 1

For more information please check this article: http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

友情链接