CODEWITHSUNDEEP
sqloracle-database
V V
V V

Reputation: 169

Set sequential numbers to columns of group of rows

I have a table looking like this:

*  user_id   index
1   1      (null)
2   2      (null)
3   2      (null)
4   2      (null)
5   3      (null)
6   3      (null)

I need to set the index column so that there are sequential numbers for every users:

*  user_id   index
1   1      1
2   2      1
3   2      2
4   2      3
5   3      1
6   3      2

I tried something like this

create sequence my_seq;
UPDATE my_table SET index=my_seq.nextval WHERE USER_ID=3;

but clearly I can't do this for each ID. Is there a way to do this for all the records?

Thanks.

Upvotes: 1

Views: 53

Answers (1)

MT0
MT0

Reputation: 167981

You can use a MERGE statement with the ROW_NUMBER analytic function:

MERGE INTO my_table dst
USING (
  SELECT ROWID rid,
         ROW_NUMBER() OVER ( PARTITION BY user_id
                             ORDER BY /*Your order condition*/ ROWNUM ) AS rn
  FROM   my_table
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET "index" = src.rn WHERE "index" IS NULL;

Upvotes: 4

Related Questions