使用唯一的递增值更新表中的 int 列 [英] Update int column in table with unique incrementing values

问题描述

我试图用每行唯一的值填充 InterfaceID (INT) 列中缺少值的任何行.

I am trying to populate any rows missing a value in their InterfaceID (INT) column with a unique value per row.

我正在尝试执行此查询:

I'm trying to do this query:

UPDATE prices SET interfaceID = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) 
       WHERE interfaceID IS null

我希望 (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM services) 将针对每一行进行评估，但它只执行一次，所以我所有受影响的行都是获取相同的值而不是不同的值.

I was hoping the the (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices) would be evaluated for every row, but its only done once and so all my affected rows are getting the same value instead of different values.

这可以在单个查询中完成吗?

Can this be done in a single query?

推荐答案

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)


update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

应该做的工作

这篇关于使用唯一的递增值更新表中的 int 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！