用行号更新 Oracle 表列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33716599/
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 Oracle table column with row number
提问by Tom
I want to update a table column with row number.
Each row in empidcolumn should update with related row number.
I tried following query.
我想用行号更新表列。列中的每一行empid都应更新相关的行号。我尝试了以下查询。
UPDATE employee SET empid = row_number();
But this is not working. Any idea?
但这行不通。任何的想法？
回答by Mureinik
First, this is not the correct syntax for the row_number()function, since you're missing the overclause (resulting in an ORA-30484 error). Even if it was, this would not work, as you cannot directly use window functions in a setclause (resulting in an ORA-30483 error).
首先，这不是row_number()函数的正确语法，因为您缺少over子句（导致 ORA-30484 错误）。即使是这样，这也不起作用，因为您不能直接在set子句中使用窗口函数（导致 ORA-30483 错误）。
For this usecase, however, you could just use the rownumpseudo-column:
但是，对于这个用例，您可以只使用rownum伪列：
UPDATE employee SET empid = ROWNUM;
回答by SQLChao
You could do something like the following. You can change the ORDER BY order the rows if needed.
您可以执行以下操作。如果需要，您可以更改 ORDER BY 对行进行排序。
UPDATE emp
SET empid = emp.RowNum
FROM (SELECT empid, ROW_NUMBER() OVER (ORDER BY empid) AS rowNum FROM employee) emp
回答by Lalit Kumar B
UPDATE employee SET empid = row_number();
更新员工 SET empid = row_number();
Firstly, it is syntactically incorrect.
首先，它在语法上是不正确的。
Secondly, you cannot use ROW_NUMBER()analytic function without the analytic_clause.
其次，不能使用ROW_NUMBER()未经解析函数analytic_clause。
As you replied to my comment that the order doesn't matter to you, you could simply use ROWNUM.
当您回复我的评论时，订单对您无关紧要，您可以简单地使用ROWNUM。
UPDATE employee SET empid = ROWNUM;
It will assign the pseudo-column value by randomly picking the rows. Since you are assigning EMPID, I would suggest you should consider ordering.
它将通过随机选择行来分配伪列值。由于您正在分配EMPID，我建议您应该考虑订购。
Usually employee ids are generated using a SEQUENCEobject. There are two ways to implement the auto-increment functionality:
通常员工 ID 是使用SEQUENCE对象生成的。有两种方法可以实现自动递增功能：
- Oracle 11g and below- Auto-increment using trigger-sequence approach
- Oracle 12c- IDENTITY column autoincrement functionality
- Oracle 11g 及以下-使用触发序列方法自动递增
- Oracle 12c- IDENTITY 列自动增量功能
回答by Akshay Kadidal
you could also do this
你也可以这样做
create table your_table_name as
select row_number() over( order by 1) as serial_no, a.* from your_query a
this creates the serial number when you write the table itself. ( note this is not set as PK if you want it to act as pk)
这会在您编写表格本身时创建序列号。（请注意，如果您希望它充当 pk，则这未设置为 PK）
