Update Oracle table column with row number

问题:

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?

回答1:

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).

For this usecase, however, you could just use the rownum pseudo-column:

UPDATE employee SET empid = ROWNUM;

SQLFiddle

回答2:

You could do something like the following. You can change the ORDER BY order the rows if needed.

UPDATE emp
SET empid = emp.RowNum
FROM (SELECT empid, ROW_NUMBER() OVER (ORDER BY empid) AS rowNum FROM employee) emp

回答3:

UPDATE employee SET empid = row_number();

Firstly, it is syntactically incorrect.

Secondly, you cannot use ROW_NUMBER() analytic function without the analytic_clause.

As you replied to my comment that the order doesn't matter to you, you could simply use 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.

Usually employee ids are generated using a SEQUENCE object. There are two ways to implement the auto-increment functionality:

• Oracle 11g and below - Auto-increment using trigger-sequence approach
• Oracle 12c - IDENTITY column autoincrement functionality

回答4:

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)