How to update a column of a table to a scaling value

Question

I'm trying to update a column in my table to use the values 1 through (a max number decided by a count of records).

I don't know if I'm explaining this right, so I set up a SQLFiddle with the data I'm trying to update.

SQL FIDDLE

I want to set the Version column to 1 through (the max number). Is there some way to rewrite this query to a scale the Version number? As in, I want the first record to use 1, the second record to use 2, and so on...

UPDATE Documents
SET Version = 1

Answer 1

You can do it with a CTE and no joins:

with RankedDocument as
(
  select *
    , rn = row_number() over (order by ID)
  from Documents
)
update RankedDocument
set Version = rn

SQL Fiddle with demo.

Answer 2

From what I can tell, you want every record from Documents to have a version number which is a number moving from 1 ..... N.

You could use a temporary table and ROW_NUMBER technique to get the incremental version and then UPDATE it back to your original table.

CREATE TABLE #Temp (ID int, Version int)

INSERT INTO #Temp (ID, Version)
SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC)
FROM Documents

UPDATE Doc
SET Version = TT.Version
FROM Documents AS Doc INNER JOIN #Temp AS TT ON Doc.ID = TT.ID

DROP TABLE #Temp

If I understand you correctly..

Answer 3

Try this:

;WITH list AS (
  SELECT
      ID
    , Version = ROW_NUMBER() OVER( ORDER BY VersionID ASC )
  FROM Documents
)
UPDATE d SET  
  d.Version = x.Version
FROM Documents AS d
INNER JOIN list as x ON d.ID=x.ID

SELECT * FROM Documents

You can change the order ( ORDER BY VersionID ASC ) to the one you need.