CODEWITHSUNDEEP
sqlsql-server
Gal
Gal

Reputation: 5416

update incremental values into an SQL column

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

Upvotes: 2

Views: 1040

Answers (1)

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 5

Related Questions