用行号更新 Oracle 表列

2022-01-17 00:00:00 sql-update sql oracle oracle10g

我想用行号更新表格列.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

相关文章