mysql基于哈希环,尝试基于MySQL中的哈希删除重复的行

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

I'm going to leave the initial (oldest) nid row with the same hash.

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 |

Rows 1, and 3 would survive in this case.

解决方案

You can phrase this as a left join:

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;

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 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.

EDIT:

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;