In mysql how can I identify duplicate field values and append incremental numbers to each one

Question

So guys, I really do need help with this one:

I have this table:

---------------
mytable
---------------
id    col1
1     Winston Churchill
2     Mahatma Ghandi
3     Nnamdi Azikiwe
4     John Kennedy
5     John Paul II
6     Nelson Mandela
7     John Kennedy
8     Mikhail Gorbachev
9     John Kennedy

What I want to do is find rows with duplicated col1 and for each of them, append incremental numbers to the value of col. In the end, we should be left with this:

---------------
mytable
---------------
id    col1
1     Winston Churchill
2     Mahatma Ghandi
3     Nnamdi Azikiwe
4     John Kennedy 1
5     John Paul II
6     Nelson Mandela
7     John Kennedy 2
8     Mikhail Gorbachev
9     John Kennedy 3

http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql — Marc B, Oct 18 '16 at 18:05

score 0 · Answer 1 · answered Oct 18 '16 at 18:19

0

The problem of the duplicate col1 is only a part of your problem .. but if you don't want a trigger or a server side procedure you can try the follow query .

You should repeat this command as many times as the maximum number of repetitions ..

From time to time the repetition with the highest id receives a value equal to count, and the next repetition time being the new name is not counted

update my_table as a
inner join  ( 
       select col1, count(*)   as num, max(id) as id
      from my_table 
      group by col1
      having count(*) >1 ) t on a.col1 = t.col1 and a.id = t.1
set a.col1 = concat( a.col1, ' ',  t.num )

answered Oct 18 '16 at 18:19

ScaisEdge

131,976
10
91
107

Thanks @scaisEdge. What I need is a query I can run as a script on the command line, and I have no problem with a procedure, though I guess a trigger is not for this kind of thing. Now, the above query, can it not possibly be wrapped in something that will take care of the repetitions? – Ifedi Okonkwo Oct 18 '16 at 18:56
@IfediOkonkwo .. i don't what you can use for repeat ..could be a simple loop server side .. (php) but how many repetition you have? at max? – ScaisEdge Oct 18 '16 at 18:57
I do not know (ahead of time) the number of duplicates, and any number of different rows could have any number of duplicates. – Ifedi Okonkwo Oct 18 '16 at 20:39
then you need a server side .. program that repeat the query i provided until there are not duplicate .. Sql don't provide instruction for loop .. .. the trigger have control flow structure .. or server side language .. hope this is useful – ScaisEdge Oct 18 '16 at 21:37

In mysql how can I identify duplicate field values and append incremental numbers to each one

1 Answers1