这是我拥有的数据的子集:
|Id1|Id2|
---------
|100| 90|
|101| 90|
|102| 90|
|217|101|
|218|101|
|219|101|
但是,每当Id2发生变化时,Id2应该与Id1每次单独出现的第一次匹配,如下所示:
|Id1|Id2|
---------
|100|100|
|101|100|
|102|100|
|217|217|
|218|217|
|219|217|
如何使用update语句在数千行中更改此项?
谢谢你的帮助,这很难解释!
您可以将CTE
与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
很遗憾,FIRST_VALUE
在SQLServer2008中不可用。在这种情况下,您可以使用以下查询:
;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