How to update duplicate records using partition by without using CTE

asked 2020-08-29T19:48:43.847+00:00
Sudip Bhatt 2,246 Reputation points

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

{count} votes

Accepted answer
  1. answered 2020-08-29T20:41:18.617+00:00
    Guoxiong 7,681 Reputation points

    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;
    
    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2020-08-31T06:07:44.043+00:00
    EchoLiu-MSFT 14,416 Reputation points

    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

    No comments