Is it possible to do the following transformation in SQL?

Suppose I have the following values for match_id

1, 1, 2, 2, 3, 3...

Is it possible to use SQL 开发者_JAVA技巧to convert to the following values

1, 2, 3, 4, 5, 6...

In other words list all match_id in sequential order. For the first value perform the following operation

firstvalue * 2 - 1

For the second value perform the following operation

secondvalue * 2

Edit: This was written for SQL Server.

select
    case when (row_number() over (order by match_id)) % 2 = 1
         then match_id * 2 - 1
         else match_id * 2
    end as output_value
from
    myTable

Edit 2: To update the rows, you can use the following:

update
    T
set match_id =
    case when RowNumber % 2 = 1
         then match_id * 2 - 1
         else match_id * 2
    end
from
    (select
        *,
        (row_number() over (order by match_id)) as RowNumber
    from myTable) T

For a purely sequential numbering, this will do

;with TMP as (
    select *, row_number() over (order by match_id) rownum
    from myTable)
UPDATE TMP
SET match_id = rownum