I'm not even sure this is possible in a query, but here goes. I'm cleaning up data coming into our system from a 3rd party. They're creating a not-so-unique unique string for each person in a group that consists of the first 3 letters of the first name and then the dob in yyyymmdd format. On the few that are duplicates, I want to append -1, -2, -3 etc. to that ID so they truly are unique.
JOH19700101 0001 JOHN SMITH 1970-01-01
JOH19700101 0001 JOHNSON SMITH 1970-01-01
JOH19700101 0002 JOHN SMITH 1970-01-01
SELECT x.id, x.uid, x.group, x.name, x.dob
FROM import x
INNER JOIN (
GROUP BY group, uid
HAVING COUNT(uid) > 1) z ON x.uid = z.uid;
This will give you unique uid values although it could benefit from one small refinement. Right now the number that is appended keeps incrementing, so your first pair of dupes will get -1 and -2 appended to their uids, then the next pair -3 and -4, and so on.
It should be possible to reset @inc somehow, however as it stands this will ensure that all of your uid values are unique, and that identical uids in different groups will remain unchanged.
SET @inc :=0; UPDATE `import` i JOIN ( SELECT uid, `group` FROM `import` GROUP BY `group`, uid HAVING COUNT(uid) > 1) d ON i.uid = d.uid AND i.group = d.group SET i.uid = CONCAT(i.uid, '-',@inc:=@inc+1)