0

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
1
  • I'd want to permanently change them... – WDrgn Oct 19 '15 at 9:44
2

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).

1
  • I have both columns because it's an old application that uses them and I just have to correct a few things in it. I'll test that, thanks! – WDrgn Oct 19 '15 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.