重命名sql中的重复数据 [英] Renaming the duplicate data in sql

问题描述

我可以使用此查询找出重复的数据

I am able to find out the duplicate data using this query

   SELECT names FROM group GROUP BY names HAVING count(*) > 1

我能够获取重复的数据.我只需要知道如何将重复的数据重命名为 new

I am able to get the duplicate data.I just need to know how to rename this duplicate data with the name to new

  INPUT
+-----------------+               
| names           |
+-----------------+
| text1           |
| text2           |
| text3           |
| text1           |
| text3           |
| text4           |
+-----------------+

OUTPUT  
+-----------------+
| names           |
+-----------------+ 
| text1           |
| text2           |
| text3           |
| text1 new value |->RENAMED
| text3 new value |->RENAMED
| text4           |
+-----------------+

推荐答案

假定表上有某种主键，例如自动增量ID，则可以执行以下操作.

Assuming you have some sort of primary key on the table, like an auto increment id, you can do the following.

UPDATE group 
SET names = CONCAT(names,' Copy 1')
WHERE ID IN
(
SELECT MAX(ID) 
FROM group 
GROUP BY names 
HAVING count(*) > 1
);

为说明起见，它将查找重复的任何内容，为该集中的所有内容获取最大ID，并在其末尾附加副本1".如果您有3次或多次使用某些名称，则可能仍会留下一些重复项.只需再次运行它，这次使用副本2"而不是副本1".继续重复此过程，直到消除所有重复项.

To explain, it will find anything with a duplicate, pick up the Maximum ID for anything in that set, and append "copy 1" to the end of it. You may still have some left as duplicates if you had certain names 3 or more times. Just run it again , this time with 'copy 2' instead of 'copy 1'. Keep repeating this process until you get rid of all the duplicaates.

更新.要从@Yahia借用一个想法并使用UUID，如果要在一个查询中全部完成，可以执行以下操作.

Update. To borrow an idea from @Yahia and use UUID, you can do the following if you want to do it all in one query.

UPDATE group 
SET names = CONCAT(names, CONCAT ( ' Copy ', UUID_SHORT() ) )
WHERE 
ID NOT IN
(
SELECT MIN(ID) 
FROM group 
GROUP BY names 
HAVING count(*) > 1
) AND
ID IN
(
SELECT ID
FROM group AS G1
INNER JOIN group AS G2
ON G1.names = G2.names AND G1.ID <> G2.ID
);

这篇关于重命名sql中的重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！