如何使用递增值更新 [英] How to update with incrementing value

问题描述

我在 PostgreSQL 中有一个表，它的 ID 列应该是唯一的.但是，目前有大量行(大约 300 万行)的 ID 为1".

I have a table in PostgreSQL that has an ID column that is supposed to be unique. However, a large number of the rows (around 3 million) currently have an ID of "1".

我所知道的:

• 总行数
• ID 列的当前最大值
• ID 为1"(不正确)的行数

我需要的是一个查询，它将提取 ID 为1"的所有行，并为它们分配一个自动递增的新 ID，以便表中的每一行都有一个唯一的 ID.我希望它从 currentMaxId + 1 开始，并为每一行分配后续 ID.

What I need is a query that will pull all the rows with an ID of "1" and assign them a new ID that increments automatically so that every row in the table will have a unique ID. I'd like it to start at the currentMaxId + 1 and assign each row the subsequent ID.

这是我得到的最接近的查询:

This is the closest I've gotten with a query:

UPDATE table_name
SET id = (
    SELECT max(id) FROM table_name
) + 1
WHERE id = '1'

这样做的问题是内部 SELECT 只运行第一次，因此将有问题的行的 ID 设置为原始 max(id) + 1，而不是新的 max(id) + 1 每次都给我同样的问题，我正在努力解决.
任何关于如何调整此查询以达到我想要的结果或替代方法的建议将不胜感激！

The problem with this is that the inner SELECT only runs the first time, thus setting the ID of the rows in question to the original max(id) + 1, not the new max(id) + 1 every time, giving me the same problem I'm trying to solve.
Any suggestions on how to tweak this query to achieve my desired result or an alternative method would be greatly appreciated!

推荐答案

您可以使用临时顺序逐步完成.

You may do it step by step with a temporary sequence.

1) 创作

create temporary sequence seq_upd;

2) 将其设置为适当的初始值

2) set it to the proper initial value

select setval('seq_upd', (select max(id) from table_name));

3) 更新

update table_name set id=nextval('seq_upd') where id=1;

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