[SOLVED] How do I make duplicates in a existing column unique?

How do I make duplicates in a existing column unique?

I am using MySQL 5.6 Server. I use Navicat to work in the DB.

I have searched and found a lot of possible solutions to my problem but none of them have worked for me.

I have a table with around 36000 rows. I have a column which allows duplicate entries but I would like to make the duplicates in the row unique. Column with duplicates

I can find my duplicates using this query. But there are too many to manually edit them all. Search Result

SELECT name, COUNT(ItemTemplate_ID) 
FROM itemtemplate
GROUP BY ItemTemplate_ID
HAVING ( COUNT(ItemTemplate_ID) > 1 )

What I am looking for is a way to do one of these things.

Update the duplicates with new unique entries.

Add a text entry in another column for every duplicates. (I have a couple of empty columns I can use to add some text too.

Update the entire column with unique entries. (Doesn't matter what its called it just has to be unique.)

Thanks in advance.

Edit - There already is a unique column called Id_nb. The column I want to change entries in should not be unique.

Solution

Let me assume that you do not have a unique column in the table. If so, you can do this with variables:

set @rn := 0;
set @it := '';

update itemtemplate it
    set it.ItemTemplate_ID = (case when @it = it.ItemTemplate_ID
                                   then concat_ws('_', it.name, (@rn := @rn + 1))
                                   when @it := it.ItemTemplate_ID
                                   then if(@rn := 0, it.ItemTemplate_ID, it.ItemTemplate_ID)
                                   else if(@rn := 0, it.ItemTemplate_ID, it.ItemTemplate_ID)
                              end)
    order by it.ItemTemplate_ID;

This method does assume that there are not already columns with names like "ABC" and "ABC_1".