Menu
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?
730
Note that the UPDATE statement allows you to update as many columns as you want. Third, the WHERE clause determines which rows of the table should be updated. The WHERE clause is optional. If you omit it, the UPDATE statement will update all rows of the table. Let’s create a new table with some sample data for the demonstration.
Let’s examine the UPDATE statement in detail. First, you specify the name of the table which you want to update. Second, you specify the name of the column whose values are to be updated and the new value. If you update more than two columns, you separate each expression column = value by a comma.
To changes existing values in a table, you use the following Oracle UPDATE statement: UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ...
For this usecase, however, you could just use the rownum pseudo-column: 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
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
162
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
