Finding unique values

[johnsmith153]

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?

[Buddski]

It is possible, but before you attempt to do this have you given the colour column a unique index so this doesnt occur in the future (unless I am mistaken and you want it to allow duplicates for a time)

[johnsmith153]

Ok, I'll do that.

 

Do you have the answer?

[Buddski]

I have many solutions, not answers.

Do you want to do this in PHP or straight MySQL?

[johnsmith153]

MySQL

[Buddski]

Ok, Well I have just had a play around and have come up with something that will work but its far from perfect, it may (read: will) require some changes, unless you are happy with the result.

SET @count = 0;
UPDATE `table` JOIN (
SELECT `colour` FROM`table`
GROUP BY `colour`
    	HAVING COUNT(*) > 1
) AS `dupes` ON `chas`.`colour` = `dupes`.`colour`
SET `table`.`colour`= CONCAT(`table`.`colour`,@count := @count + 1)

The issue this code has is that "count" does not get returned to 0 when the GROUPED `colour` changes.

So from your example, the output would be something like

1 / brown1

2 / blue

3 / red2

4 / red3

5 / brown4

 

To counter this, you could add a WHERE clause and do each colour individually.

(Remember, if running this from PHP: mysql_query only supports a single query per call of the function)

[johnsmith153]

Thanks for offering this solution.

 

The problem is that the 'colour' field could be anything, so I really need a solution where it automatically picks up all duplicated rather than needing to know a set list of colours.

 

Would it help to have PHP assist in some way rather than doing it all in MySQL?

[Buddski]

You could do it in PHP, a quick select

SELECT `colour` FROM`table`
GROUP BY `colour`
    	HAVING COUNT(*) > 1

Will return all the colours that have a duplicate value.

You could then loop through this result set and use it to execute the query I posted above, OR use PHP to keep track of the count and individually update them.