使用基于同一表内现有值的增量值进行更新

Question

对于下面的表，每当向表中添加新记录时，我希望运行一条更新语句来填充"order_id"对于"order_id"中没有使用过的下一个增量值可用的相关*记录。在同一张桌子上。例如，如果max(order_id)是25，那么当运行更新时，它会用26更新相关的*新记录(即25 +1 = 26)，然后用27更新下一个相关记录，等等

相关记录是新添加到表中的具有"at_home = Y"的记录。

在下面的结构中，我声明@Last_order_id作为起始值(它将是26(=25+1))，用于更新:

DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list ) + 1  -- this gives 26

然后在我的脑海中，更新将更新第一个相关记录26，但是它跳过一个并放置27。
如果我去掉"+ 1"在声明语句，然后它的工作，因为我想要它，但它没有意义给定@last_order_id = 25，如果SET order_id = @LAST_order_id我希望它把25!

drop table growing_list
create table growing_list (order_id int, info varchar(30), at_home varchar(10))
insert into growing_list (order_id , info , at_home )
select '3' as order_id, 'Peter' as info, 'Y' as at_home union
select null as order_id, 'Missing_Bob' as info, 'Y' as at_home union
select '25' as order_id, 'Tall' as info, 'Y' as at_home union
select '10' as order_id, 'dark' as info, 'Y' as at_home union
select null as order_id, 'Missing_Jo' as info, 'N' as at_home union
select null as order_id, 'Missing_Gar' as info, 'Y' as at_home union
select null as order_id, 'Missing_Gar' as info, 'N' as at_home 
select * from growing_list
SELECT MAX(order_id) FROM growing_list -- 25
--DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) + 1 FROM growing_list )  -- first update should be 26 (25 +1) but gives 27
--DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list ) + 1 -- first update should be 26 (25 +1) but gives 27
DECLARE @LAST_order_id INT = ( SELECT MAX(order_id) FROM growing_list )  -- first update should be 26 (25 +1) this gives 26 as desired

UPDATE [dbo].[growing_list]
SET order_id = @LAST_order_id, @LAST_order_id = @LAST_order_id + 1
WHERE order_id is null
and at_home = 'Y'
select * from growing_list

Answer 1

将order_id设置为int id，并且不包含在insert语句中。它将以自动递增的方式创建。即:

drop table growing_list;
create table growing_list (order_id int identity not null primary key, 
info varchar(30), at_home varchar(10));
insert into growing_list (info , at_home )
values
('Peter','Y'),
('Missing_Bob', 'Y'),
('Tall', 'Y'),
('dark', 'Y'),
('Missing_Gar', 'Y');
select * from growing_list;

如果你能解释为什么你想为'N'插入null，那么我会建议另一种方法，比如使它成为一个普通的int字段，然后首先将'Y'添加到一个临时表中，其标识从max+1开始。