Update multiple second Id's to match the first Id's of a series

Question

This is a subset of data I have:

|Id1|Id2|
---------
|100| 90|
|101| 90|
|102| 90|
|217|101|
|218|101|
|219|101|

But Id2 should match the first occurrence of each separate occurrence of Id1, whenever Id2 changes, like this:

|Id1|Id2|
---------
|100|100|
|101|100|
|102|100|
|217|217|
|218|217|
|219|217|

How can I change this in thousands of rows with an update statement?

Thanks for your help, this was difficult to explain!

Answer 1

You can use CTE with usage of FIRST_VALUE:

;WITH ToUpdate AS (
   SELECT Id1, Id2,
          FIRST_VALUE(Id1) OVER (PARTITION BY Id2 ORDER BY Id1) AS newValue
   FROM mytable
)
UPDATE ToUpdate
SET Id2 = newValue

Well, unfortunately FIRST_VALUE isn't available in SQL Server 2008. In this case you can use the following query:

;WITH CTE_Rn AS (
   SELECT Id1, Id2,
          ROW_NUMBER() OVER (PARTITION BY Id2 ORDER BY Id1) AS rn
   FROM mytable
), ToUpdate AS (
   SELECT t1.Id1, t2.Id2, t2.Id1 AS newValue
   FROM mytable AS t1
   JOIN CTE_Rn AS t2 ON t1.Id2 = t2.Id2 AND t2.rn = 1
)
UPDATE ToUpdate
SET Id2 = newValue