0

I have the following table:

X_ID  X_NAME  X_TYPE  X_SORT_ID
 10    BOOK     1       NULL  
 20    PEN      1       NULL
 30    WATCH    2       NULL
  5    TENT     3       NULL

What I'm trying to achieve is to populate the X_SORT_ID column with incremented values starting with 1 based on value in X_ID. So the table would look like this:

X_ID  X_NAME  X_TYPE  X_SORT_ID
 10    BOOK     1       2  
 20    PEN      1       3
 30    WATCH    2       4
  5    TENT     3       1

I need to update this table only for all existing rows. The records that will be added in the future will use a sequence that would set the X_SORT_ID field to the next value.

The only query I came up with is not exactly what I need.

UPDATE X_ITEMS 
SET X_SORT_ID = (SELECT MAX(X_ID) FROM X_ITEMS) + ROWNUM
WHERE X_SORT_ID IS NULL;

I could use just a rownum, but this would assign value of 4 to the last record with X_ID = 5, which is not what I wanted.

I'd be thankful for any suggestions.

user272735
  • 10,473
  • 9
  • 65
  • 96
Grentley
  • 315
  • 3
  • 6
  • 19

2 Answers2

3

Can use oracle row_number :

update query

update items ot
set X_SORT_ID = 
( 
  select rw from 
  ( 
    select X_ID, row_number() over ( order by X_ID ) as rw from items
  ) it
  where it.X_ID = ot.X_ID 
)
;

result table

+------+--------+--------+-----------+
| X_ID | X_NAME | X_TYPE | X_SORT_ID |
+------+--------+--------+-----------+
|   10 | BOOK   |      1 |         2 |
|   20 | PEN    |      1 |         3 |
|   30 | WATCH  |      2 |         4 |
|    5 | TENT   |      3 |         1 |
+------+--------+--------+-----------+

sqlfiddle

amdixon
  • 3,814
  • 8
  • 25
  • 34
1

Using ROWNUM (a pseudocolumn) instead of ROWNUMBER(an analytic function) as used above.

Read here for difference

X_ID should be defined as primary key.

update Grentley GY
set X_SORT_ID = 
(select rno from 
     (select X_ID,rownum as rno from Grentley GY 
      order by x_id ) AB
where AB.X_ID= GY.X_ID
) ;

SQL Fiddle

Sample

Community
  • 1
  • 1
mahi_0707
  • 1,030
  • 9
  • 17