sql - Change duplicate value in a column

Question

Can you please tell me what SQL query can I use to change duplicates in one column of my table? I found these duplicates:

SELECT Model, count(*) FROM Devices GROUP BY model HAVING count(*) > 1;

I was looking for information on exactly how to change one of the duplicate values, but unfortunately I did not find a specific option for myself, and all the more information is all in abundance filled by deleting the duplicate value line, which I don't need. Not strong in SQL at all. I ask for help. Thank you so much.

question from:https://stackoverflow.com/questions/65915919/change-duplicate-value-in-a-column

Answer 1

You can easily use a Window Functions such as ROW_NUMBER() with partitioning option in order to group by Model column to eliminate the duplicates, and then pick the first rows(rn=1) returning from the subquery such as

WITH d AS
(
 SELECT *, ROW_NUMBER() OVER (PARTITION BY Model) AS rn
   FROM Devices 
)
SELECT ID, Model -- , and the other columns
  FROM d
 WHERE rn = 1 

Demo