SQL 使用唯一的递增值更新表中的 int 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13629382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update int column in table with unique incrementing values
提问by Malcolm O'Hare
I am trying to populate any rows missing a value in their InterfaceID (INT)column with a unique value per row.
我试图InterfaceID (INT)用每行唯一的值填充列中缺少值的任何行。
I'm trying to do this query:
我正在尝试执行此查询：
UPDATE prices SET interfaceID = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)
WHERE interfaceID IS null
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.
我希望(SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)对每一行都进行评估，但它只完成一次，所以我所有受影响的行都得到相同的值而不是不同的值。
Can this be done in a single query?
这可以在单个查询中完成吗？
回答by WKordos
declare @i int = SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices
update prices
set interfaceID = @i , @i = @i + 1
where interfaceID is null
should do the work
应该做的工作
回答by ram nainar
DECLARE @IncrementValue int
SET @IncrementValue = 0
UPDATE Samples SET qty = @IncrementValue,@IncrementValue=@IncrementValue+1
回答by Developer
simple query would be, just set a variable to some number you want. then update the column you need by incrementing 1 from that number. for all the rows it'll update each row id by incrementing 1
简单的查询是，只需将变量设置为您想要的某个数字。然后通过从该数字增加 1 来更新您需要的列。对于所有行，它将通过增加 1 来更新每行 id
SET @a = 50000835 ;
UPDATE `civicrm_contact` SET external_identifier = @a:=@a+1
WHERE external_identifier IS NULL;
回答by HamedKhan
In oracle-based products you may use the following statement:
在基于 oracle 的产品中，您可以使用以下语句：
update table set interfaceID=RowNum where condition;
回答by Nikhil Bhardwaj
For Postgres
对于 Postgres
ALTER TABLE table_name ADD field_name serial PRIMARY KEY
REFERENCE: https://www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm
参考：https: //www.tutorialspoint.com/postgresql/postgresql_using_autoincrement.htm
回答by Kevin Swann
Assuming that you have a primary key for this table (you should have), as well as using a CTE or a WITH, it is also possible to use an update with a self-join to the same table:
假设你有这个表的主键（你应该有），以及使用 CTE 或 WITH，也可以使用自联接到同一个表的更新：
UPDATE a
SET a.interfaceId = b.sequence
FROM prices a
INNER JOIN
(
SELECT ROW_NUMBER() OVER ( ORDER BY b.priceId ) + ( SELECT MAX( interfaceId ) + 1 FROM prices ) AS sequence, b.priceId
FROM prices b
WHERE b.interfaceId IS NULL
) b ON b.priceId = a.priceId
I have assumed that the primary key is price-id.
我假设主键是 price-id。
The derived table, alias b, is used to generated the sequence via the ROW_NUMBER() function together with the primary key column(s). For each row where the column interface-id is NULL, this will generate a row with a unique sequence value together with the primary key value.
派生表别名 b 用于通过 ROW_NUMBER() 函数和主键列生成序列。对于列 interface-id 为 NULL 的每一行，这将生成具有唯一序列值和主键值的行。
It is possible to order the sequence in some other order rather than the primary key.
可以按其他顺序而不是主键对序列进行排序。
The sequence is offset by the current MAX interface-id + 1 via a sub-query. The MAX() function ignores NULL values.
该序列通过子查询由当前 MAX interface-id + 1 偏移。MAX() 函数会忽略 NULL 值。
The WHERE clause limits the update to those rows that are NULL.
WHERE 子句将更新限制为那些为 NULL 的行。
The derived table is then joined to the same table, alias a, joining on the primary key column(s) with the column to be updated set to the generated sequence.
然后将派生表连接到同一个表，别名 a，连接主键列，将要更新的列设置为生成的序列。
回答by Gordon Linoff
Try something like this:
尝试这样的事情：
with toupdate as (
select p.*,
(coalesce(max(interfaceid) over (), 0) +
row_number() over (order by (select NULL))
) as newInterfaceId
from prices
)
update p
set interfaceId = newInterfaceId
where interfaceId is NULL
This doesn't quite make them consecutive, but it does assign new higher ids. To make them consecutive, try this:
这并不完全使它们连续，但它确实分配了新的更高的 id。要使它们连续，请尝试以下操作：
with toupdate as (
select p.*,
(coalesce(max(interfaceid) over (), 0) +
row_number() over (partition by interfaceId order by (select NULL))
) as newInterfaceId
from prices
)
update p
set interfaceId = newInterfaceId
where interfaceId is NULL
回答by Izzy
You can try :
你可以试试 ：
DECLARE @counter int
SET @counter = 0
UPDATE [table]
SET [column] = @counter, @counter = @counter + 1```
