仅更新MySQL中重复条目的第一条记录 [英] Update only first record from duplicate entries in MySQL
问题描述
问题已解决!
更新:
不太正确,我需要在具有ID,NAME,COVER字段的简单表上进行示例
Not quite right what I need, lets do example on simple table with fields ID,NAME,COVER
我有100个条目,其中包含100个名称,其中一些名称是重复的,但是我只想从重复项中更新第一个.
I have got 100 entries with 100 names, some of the names are duplicated, but I want only update first one from duplicates.
试图从数据库中所有重复项中更新所有第一行,真的很难做到,我知道怎么做吗?下面是我要重建的代码,但是对于所有重复项,此代码将每个第一个替换为最后一个.
Trying to update all the 1st rows from all the duplicates in database, really hard to do it, any idea how I can make it? Below is the code I am trying to rebuild, but this code replace every 1st one with the last one for all the duplicates.
模式,我希望它如何在下面工作
Schema, how I want it work below
ID NAME COVER
1 Max 1
2 Max 0
3 Andy 1
4 Andy 0
5 Andy 0
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;
推荐答案
目前尚不清楚您想要什么.也许是这样:
It's not clear at all what you want. Perhaps this:
UPDATE table AS t
JOIN
( SELECT MIN(ID) MinID
FROM table
GROUP BY Name
HAVING COUNT(*) > 1
) AS m
ON t.ID = m.MinID
SET t.Cover = 1 ;
对于这个(以及将来的)问题,在编写问题时请记住:
For this (and future) question, keep in mind, when you write a question:
1. a description of your problem, as clear as possible --- you have that
2. data you have now (a few rows of the tables) --- ok, nice
3. the code you have tried --- yeah, but better use same names
--- as the data and description above
4. the error you get (if you get an error) --- doesn't apply here
5. the result you want (the rows after the update in your case)
--- so we know what you mean in case we
--- haven't understood from all the rest
这篇关于仅更新MySQL中重复条目的第一条记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!