用行号更新 Oracle 表列

Update Oracle table column with row number(用行号更新 Oracle 表列)

问题描述

我想用行号更新表格列.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 表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！