Change non unique values in database

Databases
1

I have a simple db with values.

I want to find all duplicate values and rename them 1,2,3 - so if I have these values for a ‘colour’ field:

pID / color

1 / brown
2 / blue
3 / red
4 / red
5 / brown

…would result in:

1 / brown1
2 / blue
3 / red1
4 / red2
5 / brown2

…is this possible?

2

not easily, no

3

Could you possibly help a little more on this?

What do you advise?

4

What server-side language are you using?

5

i would look long and hard at the difference between “brown” and “brown1” and examine why you feel your table needs to include bofadem

of course, i realize the you don’t actually have “brown” and “brown1” but since i don’t know what your actual data really is, i don’t think i can be of very much help

6

It’s to create unique usernames. Previously users entered a username but this was never used or checked for uniqueness (just collected in the db).

Urls use database id to display profiles, but now I want to use the username (so must be unique). There are other uses where I need a unique username as well. Some aren’t unique and I need to make them unique. The idea is to add a number to the end of each one that isn’t unique.

Anyone have an idea? Can this be done completely in MySQL without PHP assistance?

If you can’t or don’t know thanks anyway and no need to post. Thanks again.

John

7

you appear to have an “id” column, called pID

if this is an auto_increment column, it is guaranteed unique

why not append it to the username, instead of a sequential number, for duplicate usernames

8

The trouble is there are 47,000 records, and uniques are more likely be a maximum of 30 odd, so xxxxx30 sounds better than xxxxx32876.

Is there really no simple DB query to do this?

Thanks for the help r937.

John.

9

oh?

no