SQL Server：使用变量增加INT列

Trying to insert an order column into some records, based on another field. Normally not a problem in MySQL, but in SQL Server I can't quite understand the syntax here.

尝试根据另一个字段将订单列插入到某些记录中。通常在MySQL中不是问题,但在SQL Server中我不太明白这里的语法。

This is what I have:

这就是我所拥有的:

DECLARE @a int
SET @a = 1

UPDATE tablename 
SET order_position = @a:=@a+1  
WHERE table_id = xxx

But part of me thinks this is going down the route of a function/procedure as opposed to a one hit UPDATE query.

但是我的一部分认为这是一个函数/过程的路径,而不是一个命中UPDATE查询。

Sorry, but I wrote this as a MySQL database person, not familiar with variables with SQL Server so could be a little wrong.

对不起,但我把它写成了一个MySQL数据库人,不熟悉SQL Server的变量,所以可能有点不对。

I need to run this on a load of records one by one, and I want the order_position column to be 1-7 (where there are 7 records), etc..

我需要逐个在一堆记录上运行它,我希望order_position列为1-7(其中有7条记录)等。

Thanks, Chris

2 个解决方案

#1

Try this code:

试试这段代码:

DECLARE @a int
SET @a = 1
UPDATE tablename SET order_position = @a, @a=@a+1  WHERE table_id = xxx

You are trying to do a double assignment which is the issue. "order_position = @a:=@a+1" has 2 interpretations, one you know and the other is that the result of incrementing a, that it succeeded is what should go in the order_position value.

你正在尝试进行双重任务,这就是问题所在。 “order_position = @a:= @ a + 1”有2个解释,一个是你知道的,另一个是递增a的结果,它成功的是order_position值。

#2

Separate the variable incrementing from the field update.

将变量递增与字段更新分开。

DECLARE @a int
SET @a = 1
UPDATE tablename 
SET order_position = @a
   ,@a = @a + 1  
WHERE table_id = xxx

Coming from MySQL you may be overlooking a great tool for this task, ROW_NUMBER().

来自MySQL你可能会忽略这个任务的一个很好的工具,ROW_NUMBER()。

You can use ROW_NUMBER() to assign a number to each row in a table:

您可以使用ROW_NUMBER()为表中的每一行分配一个数字:

SELECT *,ROW_NUMBER() OVER (PARTITION BY ....  ORDER BY .... )
FROM Table

PARTITION BY indicates a grouping for numbering, ie there will be a '1' for each combination of fields used in the PARTITION BY, and they will of course be ordered from 1-n based on the ORDER BY clause.

PARTITION BY表示编号的分组,即PARTITION BY中使用的每个字段组合都有一个'1',它们当然会根据ORDER BY子句从1-n开始排序。

#1