BlackHair BlackHair - 3 months ago 8
SQL Question

how to increase character sequence in sql if given random number of table rows?

I'm currently studying SQL language in Oracle.
After making very simple

STUDENT
table, I thought about how to make character sequence in
ID
field.

For example, if
STUDENT
table has 6 rows, I want the
ID
field to be inserted by
'a','b','c'...'f'
characters respectively. And another condition is that the
ID
sequence should be ordered by age in ascending order.

The below explanation is about
STUDENT
table description and current inserted value (
ID
field is currently empty).

NAME AGE GRADE ID
hi 15 1
dui 12 2
giyu 16 3
hero 27 4
power 55 3
rai 37 4


///////////////////////////////////////////////////////////////////////////////////////////////////////

DESC STUDENT
NAME VARCHAR2(20)
AGE NUMBER(5)
GRADE NUMBER
ID VARCHAR2(12)


I hope many brilliant ideas come up here =)

until now, this is very easy to come up with making table ordered by age.
but inserting character sequence respectively is ... well .. idea doesn't come up now. And this is not homework. i just want to practice sql language.

Answer
update tableX X
   set ID=(
           select ID from (
                select rowid as rid,
                       chr(mod((row_number() over (order by age))-1,26)+97) as ID
                  from tableX T
               )
            where rid=X.rowid
          )

Required order of the ID set in the over(order by ) clause. Function row_number() gets sequence number of rows in given order. mod() gets remainder of the division (for 26 chars only). chr() get char by the ascii code.