Renaming the duplicate data in sql

I am able to find out the duplicate data usin开发者_如何学Pythong this query

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

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

---

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
);

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.

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
);

---

Try this one -

UPDATE table1 n 
  JOIN (SELECT names FROM table1 GROUP BY names HAVING count(*) > 1) d
    ON n.names = d.names
SET n.names = 'new value';

EDIT:

Full code -

CREATE TABLE table1(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  names VARCHAR(255) DEFAULT NULL
);    

INSERT INTO table1 VALUES 
  ('text1'),
  ('text2'),
  ('text3'),
  ('text1'),
  ('text3'),
  ('text4');

UPDATE table1 n 
  JOIN (SELECT names, MIN(id) min_id FROM table1 GROUP BY names HAVING COUNT(*) > 1) d
    ON n.names = d.names AND n.id <> d.min_id
SET n.names = CONCAT(n.names, ' new value');

SELECT * FROM table1;

+----+-----------------+
| id | names           |
+----+-----------------+
|  1 | text1           |
|  2 | text2           |
|  3 | text3           |
|  4 | text1 new value |
|  5 | text3 new value |
|  6 | text4           |
+----+-----------------+

---

use (corrected as per comment)

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 
GROUP BY names 
HAVING count(*) > 1
);

this makes all duplicates unique with one execution by concatenating a unique UUID_SHORT... except the one with the smallest ID - it stays untouched...