Update duplicate rows in table

Hi,

I have this script to list duplicate rows in my table:

SELECT *
FROM lin
WHERE ids IN
(
      SELECT ids
      FROM lin
      GROUP BY ids
      HAVING COUNT(*) > 1
)
ORDER BY ids

I need to update the duplicate vales and add a number to have different value. I tried this but not work like a select, how can change the query to update?

update lin set  IdS=IdS + convert(varchar,Row_Number() Over ( Order By lin.ids ))
WHERE ids IN
(
      SELECT ids
      FROM lin
      GROUP BY ids
      HAVING COUNT(*) > 1
)
rflorencioAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Yeah, tricky one...

Might have to use a cursor or a CTE because the row_number will potentially change after each individual update, but the rows arent committed until after the update - catch-22.

do the ids have to be in sequence ?
are they just a plain int datatype ?
because they have been duplicated is it safe to assume they are not identity ?
is there any referential integrity (explicit or implied) hanging off the values of ids ?

What about if we get the MAX(ids) as a starting point - and add from there ?
0
 
rflorencioAuthor Commented:
Hi,

I've  a solution based in your answer.

thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.