如何将表的列更新为缩放值

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).

我正在尝试更新表中的列以使用值1到（由记录数决定的最大数量）。

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

我不知道我是否正确解释这一点，所以我设置了一个SQLFiddle，其中包含我正在尝试更新的数据。

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...

我想将Version列设置为1到（最大数量）。有没有办法将此查询重写为版本号的缩放？在，我希望第一个记录使用1，第二个记录使用2，依此类推......

UPDATE Documents
SET Version = 1

3 个解决方案

#1

You can do it with a CTE and no joins:

您可以使用CTE并且没有连接：

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

SQL Fiddle with demo.

SQL小提琴演示。

#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.

据我所知，你希望来自Documents的每条记录都有一个版本号，这是一个从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.

您可以使用临时表和ROW_NUMBER技术来获取增量版本，然后将其更新回原始表。

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..

如果我理解正确的话..

#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.

您可以将订单（ORDER BY VersionID ASC）更改为您需要的订单。

#1