34,593
社区成员
发帖
与我相关
我的任务
分享
-- 适应前提:一个ID最多只有2条,并且[某个列]必须不等。
UPDATE table1
SET ID = ID + 50000
WHERE EXISTS (SELECT *
FROM table1 t
WHERE t.ID = table1.ID
AND t.某个列 < table1.某个列
)
CREATE TABLE TEST1
( id int,
number int
)
go
insert into TEST1(id,number)
select 1,1
union
select 1,2
union
select 2,3
union
select 2,4
select id,number,ROW_NUMBER() OVER(PARTITION BY id order by number) as RN
INTO #temp
FROM TEST1
UPDATE a
SET a.id=a.id+5000
FROM TEST1 a join #temp b
ON a.id=b.id and a.number=b.number
WHERE b.RN<>1
DROP TABLE #temp
SELECT * FROM TEST1