SQL-如何以每行值增加1来更新列? [英] SQL - how to update a column with each row value incremented by 1?

问题描述

对于列中的选定行，如何从头到尾顺序更新每一行，每行值增加1(或一定数量).我知道这可以在excel中完成，但我可以弄清楚如何在SQL Server中实现.例如:

For the selected rows in a column, how to update each row sequentially from the beginning to the end, with each row value incremented by 1 (or certain number). I know this can be done in excel in few seconds but I could figure out how to achieve in SQL server. For instance:

客户ID现在为NULL 更新客户ID，每行增加1(即第一行= 1，第二行= 2，..... nth行= n)

customer id is NULL now update customer id with every row incremented by 1 (i.e. first row = 1, second row = 2, .....nth row = n)

ship-to party   customer id 
0002018092      NULL
0002008127      NULL
0002000129      NULL
0002031592      NULL
0002034232      NULL

所需的输出

ship-to party   customer id
0002018092      1
0002008127      2
0002000129      3
0002031592      4
0002034232      5

此外，对于列中的选定行，如何用行号更新每一行?我知道有一个row_number()函数，但未成功产生所需的结果.例如

Also, for the selected rows in a column, how to update each row with the row number? I know there is a row_number() function but didn't succeed in producing the desired result. for instance

列A现在为NULL 用每行递增1来更新A列(即第一行=行号1，第二行=行号2，.... n行=行号n)

column A is NULL now update Column A with every row incremented by 1 (i.e. first row = row number 1, second row = row number 2, .....nth row = row number n)

任何示范都将非常有帮助.thkans

Any demonstration would be very helpful.thkans

推荐答案

示例:假设我想向表tblUser

有两种简单的方法

首先:这只会将值1添加到每一列SomeIntField

first: this just adds value 1 to each column SomeIntField

update tblUser set SomeIntField = SomeIntField + 1

second:这将增加一个增量值，第一行获取+1，第二行获取+2，依此类推...

second : this adds an incrementing value, the first row gets +1, second gets +2, and so on...

declare @number int = 0

update tblUser
set    @number = @number + 1,    
       SomeIntField = isnull(SomeIntField, 0) + @Number

根据您的最新评论，这可能是您想要的

declare @table table (shiptoparty varchar(50), customer_id int)

insert into @Table (shiptoparty, customer_id)
values ('0002018092', NULL), ('0002008127', NULL), ('0002000129', NULL), ('0002031592', NULL), ('0002034232', NULL)

declare @number int = 0

update @table
set    @number = @number + 1,    
       customer_id = isnull(customer_id, 0) + @Number

select * from @table

其结果是:

shiptoparty | customer_id   
----------- | -----------   
0002018092  | 1 
0002008127  | 2 
0002000129  | 3 
0002031592  | 4 
0002034232  | 5 

这篇关于SQL-如何以每行值增加1来更新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！