增加变量的 PL/SQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8052786/
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
PL/SQL Update that increments a variable
提问by typhoid
I found the following approach on http://haacked.com/archive/2004/02/28/sql-auto-increment.aspxbut have had no luck with PL/SQL. Any thoughts?
我在http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx上找到了以下方法，但对 PL/SQL 没有运气。有什么想法吗？
update table
set table.column = var1 = var1 + 1
where table.column2 = "value';
It appears not to like the incrementing of var1 in the second half od the set line
似乎不喜欢在设置行的后半部分增加 var1
回答by APC
A bit of PL/SQL to hold the original max ID. Using ROWNUM solves the problem of incrementing a count:
一点 PL/SQL 来保存原始的最大 ID。使用 ROWNUM 解决了递增计数的问题：
declare
n pls_integer;
begin
select max(col1)
into n
from your table;
update your_table
set col1 = n + rownum
where col2 = 'value';
end;
This will give you a unique ID for COL1 which won't collide with COL1 for other value of COL2.
这将为您提供 COL1 的唯一 ID，该 ID 不会与 COL1 与 COL2 的其他值发生冲突。
回答by Allan
Based on what's going on in the link you provided, I think what you really want is this:
根据您提供的链接中发生的事情，我认为您真正想要的是：
update your_table
set your_table.column = rownum
where your_table.column2 = 'value';
This will set each row in the table for 'value' to a unique integer, starting at 1. The values will only be unique for that value however. You could add rownum to the current max value to set them all greater than any existing values (as seen in @APC's answer).
这会将表中“值”的每一行设置为一个唯一的整数，从 1 开始。但是，这些值仅对于该值是唯一的。您可以将 rownum 添加到当前最大值以将它们设置为大于任何现有值（如@APC 的回答中所示）。
It should be noted that this is not safe to do if there's any chance of multiple inserts and/or updates on this column happening simultaneously (i.e. it's probably okay to do this as a one-time fix, but it should not be a regular routine (and certainly shouldn't be used in code)). In that case you should definitely be using a sequence.
应该注意的是，如果此列有可能同时发生多个插入和/或更新，则这样做是不安全的（即，将其作为一次性修复可能没问题，但它不应该是常规例程（当然不应该在代码中使用））。在这种情况下，您绝对应该使用序列。
回答by Lost in Alabama
This might work better for you:
这可能更适合您：
update table
set table.column = table.column + 1
where table.column2 = "value';
Sounds like what you need is what @Wolf said, a sequence. Otherwise you could do this:
听起来你需要的是@Wolf 所说的，一个序列。否则你可以这样做：
update table
set table.column =
(select max(table.column) + 1
from table)
where table.column2 = "value';
Hopefully the column2 values are unique, or else you will get duplicates.
希望 column2 值是唯一的，否则你会得到重复。
