将增量值更新到SQL列

I'm looking for some help with the following query:

我正在寻找以下的帮助:

I have a bunch of rows with column TYPE, VERSION - however in some cases the version got messed up so I want to rewrite it.

我有一堆列类型的行，但是在某些情况下，这个版本被搞砸了，所以我想重写一下。

Basically, it now looks this:

基本上，现在看起来是这样的:

Type, Version
A, 0
A, 0
A, 1
A, 2
B, 1
B, 3

I want it to look like this:

我想让它看起来像这样:

Type, Version
A, 0
A, 1
A, 2
A, 3
B, 0
B, 1

Any help would be greatly appreciated, Thanks

非常感谢您的帮助，谢谢

1 个解决方案

WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Version) - 1 AS V
FROM YourTable       
)
UPDATE T 
SET Version = V

WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Version) - 1 AS V
FROM YourTable       
)
UPDATE T 
SET Version = V