Comparing id s of same column and assign value of one to another

by user187664   Last Updated August 14, 2019 12:06 PM

I have table with

id name 
1  aa
1  bb
2  ff
3  df 

I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row Expected:

id name
1   aa
1   aa
2   ff
3   df 

Can any one suggest how this can be achieved?

Tags : sql-server


Answers 1


WITH cte AS (SELECT *, 
                    MIN(name) OVER (PARTITION BY id 
                                    ORDER BY name ASC) rn 
             FROM test)
UPDATE cte 
SET name=rn

fiddle

This will work on the basis of the "lower" letters (IE 'A') will overwrite "higher" letters (IE 'B'), as you have not indicated any preference for how to handle this ordering.

Akina
Akina
August 14, 2019 11:32 AM

Related Questions


creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM