Update rows in a table with an index column starting from 0 with consecutive numbers

Question

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a script to identify the rows who do not follow this rule but I don't know how to modify those rows (ids). The script to identify the rows is:

select ID,NRCRT,H_ID,INDEX_COLUMN from ANEXE_H_ROWS where H_ID in ( 
select anexa.id from (
    select ANEXA_ROWS.H_ID as id, ANEXA_ROWS.INDEX_COLUMN as idx
        from chest CH 
        inner join investig INV on INV.FK_ID_CHEST = CH.ID
        inner join tip_investig TIP_INV on TIP_INV.ID = INV.FK_TIP_INVESTIG 
        inner join ANEXE_H ANEXA on ANEXA.FK_ID_INVESTIG = INV.ID
        inner join ANEXE_H_ROWS ANEXA_ROWS on ANEXA_ROWS.H_ID = ANEXA.ID
    ) anexa
group by anexa.id having max(anexa.idx)+1<>count(*) or min(anexa.idx)<>0
)
order by H_ID,INDEX_COLUMN

and let's say it returns:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    8       1136    7
2518    9       1136    8
2519    10      1136    9
2520    11      1136    10
2521    12      1136    11
2522    13      1136    12
2523    14      1136    13

3000    2       1137    1
3001    3       1137    2
3002    4       1137    3

How can I modify the script to alter the table to return:

ID      NRCRT   H_ID    INDEX_COLUMN
2517    1       1136    0
2518    2       1136    1
2519    3       1136    2
2520    4       1136    3
2521    5       1136    4
2522    6       1136    5
2523    7       1136    6

3000    1       1137    0
3001    2       1137    1
3002    3       1137    2

Answer 1

To update the values in the table, you can use a MERGE statement (alter is for changing the structure of a table, not for changing values. That is done with UPDATE or MERGE)

merge into anexe_h_rows 
using (
  select id, 
         row_number() over (partition by h_id order by nrcrt) as rn
  from anexe_h_rows
) t ON (t.id = anexe_h_rows.id)
when matched then 
  update set nrcrt = rn, index_column = rn - 1;

This assumes that anexe_h_rows.id is the primary key of that table.

It also assumes that value for the column nrcrt is always one bigger than the one for index_column (which raises the question why you have both - because one can easily be derived from the other).