Update Oracle table column with row number

Question

I want to update a table column with a row number. Each row in the empid column empid be updated with a corresponding row number. I tried the following query.

 UPDATE employee SET empid = row_number(); 

But that does not work. Any idea?

Answer 1

Firstly, this is not the correct syntax for the row_number() function, since you are missing the over clause (as a result, the ORA-30484 error occurs). Even if that were the case, it would not work, since you cannot directly use the window functions in the set clause (which leads to ORA-30483 error).

For this, however, you can simply use the rownum pseudo-column:

 UPDATE employee SET empid = ROWNUM; 

SQLFiddle

Answer 2

You can do something like the following. You can change the ORDER BY to arrange the rows if necessary.

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

Answer 3

Employee UPDATE SET empid = row_number ();

Firstly, this is syntactically incorrect.

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

As you answered my comment that the order does not matter to you, you can simply use ROWNUM .

 UPDATE employee SET empid = ROWNUM; 

It assigns a pseudocolonomer value to a random string selection. Since you are assigning an EMPID , I would suggest that you consider the order.

Typically, employee identifiers are generated using the SEQUENCE object. There are two ways to implement the auto zoom feature:

• Oracle 11g and below - Auto increment using trigger sequence approach
• Oracle 12c - IDENTITY Auto-Increment Functionality

Answer 4

you can also do it

 create table your_table_name as select row_number() over( order by 1) as serial_no, a.* from your_query a 

this creates a serial number when writing the table itself. (note that this is not set as PK if you want it to execute the pk function)