找到重复的mysql记录,并用最后一个重复值更新第一个副本 [英] Find duplicate mysql record and update first duplicate with last duplicate value
问题描述
我有一张这样的表。
ID Name Tag
1 Orange tea
2 Pear light
3 Apple tea
4 Juice tea
5 Cocoa baseball
6 Camera baseball
我想做的是对于具有重复标签的行,我想更新第一次发生的名称与LATEST发生。
What I want to do is for rows that have duplicate TAGS, I want to update the name of FIRST occurence with the LATEST occurence.
所以在上面的例子中,我想将ID 1的名称从Orange更新为Juice,并删除其他(#3,#4)
并将ID 5从Cocoa更新为Camera并删除#6。
So in example above, I would like to update ID 1's name from Orange to Juice and delete the others (#3,#4) And update ID 5 from Cocoa to Camera and delete #6.
如何使用PHP与PHP进行交互?或者可能只是在MySQL内部执行?
How would I do that using MySQL with PHP? Or maybe possible to just do it inside MySQL?
谢谢!
推荐答案
p>对于 UPDATE
查询,我们需要为每个标签获取最小和最大ID,只有有重复的行:
For the UPDATE
query, we need to get the min and max ID's for each tag, only if there are duplicate rows:
UPDATE table t
JOIN (
SELECT MinID, b.Name LatestName
FROM table b
JOIN (
SELECT MIN(ID) MinID, MAX(ID) MaxID
FROM table
GROUP BY tag
HAVING COUNT(*) > 1
) g ON b.ID = g.MaxID
) rs ON t.ID = rs.MinID
SET t.Name = LatestName;
对于 DELETE
查询,我们全部删除不属于其标签组的第一个ID的行:
For the DELETE
query, we delete all rows that are not the first ID's of its tag group:
DELETE t.*
FROM table t
LEFT JOIN (
SELECT MIN(ID) MinID
FROM table
GROUP BY tag
) g ON t.ID = g.MinID
WHERE g.MinID IS NULL;
这篇关于找到重复的mysql记录,并用最后一个重复值更新第一个副本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!