Update grouped records in Oracle with an incremental value

oracle, sql, sql-update

Solution

You should be able to use the analytic function `row_number` for this

UPDATE t_group t1
   SET group_index = (SELECT rnk
                        FROM (SELECT id,
                                     row_number() over (partition by study_id
                                                            order by id) rnk
                                FROM t_group) t2
                       WHERE t2.id = t1.id)

Problem

So a dilemna, I have an Oracle table called T_GROUP. The records in the table have a unique id (ID) and they are part of a Study, identified by STUDY_ID, so multiple groups can be in the same Study. ``` CREATE TABLE T_GROUP ( "ID" NUMBER(10,0), "GROUP_NAME" VARCHAR2(255 CHAR), "STUDY_ID" NUMBER(10,0) ) ``` The existing table has hundreds of records and I now add a new column called GROUP_INDEX: ``` ALTER TABLE T_GROUP ADD ( GROUP_INDEX NUMBER(10,0) DEFAULT(0) ); ``` After adding the column I need to run a script to update the GROUP_INDEX field as such: it should start at 1 and increment by 1 for each group within a study, starting with the lowest ID. So now I have data as follows: ``` ID GROUP_NAME STUDY_ID GROUP_INDEX ------------------------------------------- 1 Group 1 3 0 2 Group 2 3 0 3 My Group 5 0 4 Big Group 5 0 5 Group X 5 0 6 Group Z 6 0 7 Best Group 6 0 ``` After the update the group_index field should be as follows: ``` ID GROUP_NAME STUDY_ID GROUP_INDEX ------------------------------------------- 1 Group 1 3 1 2 Group 2 3 2 3 My Group 5 1 4 Big Group 5 2 5 Group X 5 3 6 Group Z 6 1 7 Best Group 6 2 ``` The update will be run from sqlplus via a batch file. I've played around with group by and sub queries but I'm not having much luck, and having never used sqlplus I'm not sure if I can use variables, cursors etc. All tips greatly appreciated!

Original source