用行号更新Oracle表列 [英] Update Oracle table column with row number
问题描述
我想用行号更新表列.
empid列中的每一行都应使用相关的行号进行更新.
我尝试了以下查询.
I want to update a table column with row number.
Each row in empid column should update with related row number.
I tried following query.
UPDATE employee SET empid = row_number();
但是这不起作用.有什么主意吗?
But this is not working. Any idea?
推荐答案
首先，这不是row_number()函数的正确语法，因为您缺少over子句(导致ORA-30484错误).即使是这样，这也行不通，因为您不能直接在set子句中使用窗口函数(导致ORA-30483错误).
First, this is not the correct syntax for the row_number() function, since you're missing the over clause (resulting in an ORA-30484 error). Even if it was, this would not work, as you cannot directly use window functions in a set clause (resulting in an ORA-30483 error).
但是，对于这种用例，您可以只使用rownum伪列:
For this usecase, however, you could just use the rownum pseudo-column:
UPDATE employee SET empid = ROWNUM;
SQLFiddle
这篇关于用行号更新Oracle表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
