T-SQL to Update duplicate records

Question

Ok Im not sure this can even be done but I have a table that has a field with duplicates in it, 970 of them to be exact.

What Id like to do is within this field, find duplicate entries and replace one of the entries by adding an 'A' or 'B' to the front or end of the string so it makes it not a duplicate. What Ive noticed is that it may not just be duplicates there may be as many as 4 of the same entries in this field.

The tricky part is that there are good entries in this field already so I cant update the entire database, I have the ID's specific to this update, so I only need the 970 entries updated.

How can I write this? How can I write a script to use the 970 ID's and only change one of the duplicate entries by adding a letter in front of it?

Thanks!

---

Russ, MCSE

Answer 1

See Uri's blog post. You can use the CTE ROW_NUMBER solution to update the duplicates only, e.g. something like

WITH Dups AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION unqiueColumns ORDER BY whateverOrder) AS RN
  FROM yourTable
)
UPDATE Dups 
SET uniqueColumns = uniqueColumns + CAST(RN AS VARCHAR(255))
WHERE RN > 1;

Answer 2

You have a duplicate in a field but some PK columns? Maybe something like

update tbl

set fld = fld + case when seq = 2 then 'A' when sew = 3 then 'B' when seq = 4 then 'C' end

from tbl a

join (select pk, seq = rownumber() over (partition by fld order by pk) from tbl) b

on  a.pk = b.pk

and b.seq <> 1

Answer 3

nice answer