尝试基于MySQL中的哈希删除重复的行 [英] Trying to delete duplicate rows based on a hash in MySQL

问题描述

我试图根据哈希值删除重复的值（所有重复的值都具有相同的nid）。

I'm trying to delete duplicate values (which will all have the same nid) based on the hash value.

我将保留首字母（

由于某种原因，我收到错误消息：您无法在FROM子句中指定目标表'node_revision'进行更新

For some reason, I get the error, "You can't specify target table 'node_revision' for update in FROM clause

我正在尝试为我的表加上别名，但这似乎不起作用-我在做什么错了？

I'm trying to alias my tables, but that doesn't seem to work - what am I doing wrong?

delete from node_revision
WHERE nid NOT IN(SELECT MIN(nid) FROM node_revision GROUP BY hash)

（时间戳只是为了说明，实际上不希望在任何查询中使用此时间戳）

(timestamp is just for illustration, don't actually want this used in any queries)

|  nid  |  hash   |  timestamp  |
|   2   | 123456  |  123364600  |
|   2   | 123456  |  123364601  |
|   2   | 1234567 |  123364602  |

在这种情况下，第1和第3行将继续存在。

Rows 1, and 3 would survive in this case.

推荐答案

您可以将其表达为左联接：

delete nr from node_revision nr left join
               (SELECT MIN(nid) as minnid
                FROM node_revision
                GROUP BY hash
               ) nrkeep
               on nr.nid = nrkeep.minnid
    where nrkeep.minnid is null;

您还可以欺骗 MySQL以使用子查询：

You can also "trick" MySQL into using the subquery:

DELETE FROM node_revision
    WHERE nid NOT IN (SELECT minnid
                      FROM (SELECT MIN(nid) as minnid FROM node_revision GROUP BY hash
                           ) t
                     );

MySQL在 update <中使用修改后的表具有明确的限制/ code>和 delete 语句。该查询通过使用子查询实际体现出minnids的列表来克服限制。

MySQL has a well-documented limitation on using the modified table in update and delete statements. This query gets around the limitation by actually materializing the list of minnids by using a subquery.

编辑：

根据问题中的示例，您应按以下方式使用 timestamp ：

Based on the example now in the question, you should use timestamp as follows:

delete nr from node_revision nr left join
               (SELECT hash, nid, min(timestamp) as mintimestamp
                FROM node_revision
                GROUP BY hash
               ) nrkeep
               on nr.hash = nrkeep.hash and
                  nr.nid = nrkeep.nid and
                  nr.timestamp = nrkeep.mintimestamp
    where nrkeep.minnid is null;

这篇关于尝试基于MySQL中的哈希删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！