How to update duplicate records using partition by without using CTE

Question

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 1

If uniqueColumns is unique, you need to make sure the column is still unique after the updates:

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

Answer 2

Hi @Sudip Bhatt ,
Derived table and cte have similar functions,please refer to:

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

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Best Regards
Echo