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

group byjoin;oracleselect

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

Best Answer

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

Related Solutions

Mysql – Count consecutive null rows from a joined table

This is essentially a gaps-and-islands problem. And when I have my SQL Server hat on, I often solve this kind of problem with two ROW_NUMBER() calls. Sadly, MySQL, unlike many other major SQL products, does not support ROW_NUMBER(), nor any other ranking function. To make up for that, however, you can use variable assignment in SELECTs, which MySQL does support (unlike many other major SQL products).

Below is a solution followed by an explanation:

SELECT
  member_id,
  member_name,
  event_id,
  COUNT(*) AS consecutive_times_missed,
  MIN(event_date) AS first_date_missed,
  MAX(event_date) AS last_date_missed
FROM (
  SELECT
    member_id,
    member_name,
    event_id,
    event_date,
    is_missed,
    @occ_ranking := (event_id = @last_event) * (member_id = @last_member) * @occ_ranking + 1,
    @att_ranking := (event_id = @last_event) * (member_id = @last_member)
                                             * (is_missed = @last_missed) * @att_ranking + 1,
    @occ_ranking - @att_ranking AS grp,
    @last_member := member_id,
    @last_event  := event_id,
    @last_missed := is_missed
  FROM (
    SELECT
      m.member_id,
      m.member_name,
      e.event_id,
      e.event_date,
      (a.attendance_date IS NULL) AS is_missed
    FROM       members     m
    INNER JOIN event_dates e ON m.member_join_date <= e.event_date
    LEFT  JOIN attendance  a ON m.member_id = a.member_id
                            AND e.event_id = a.event_id
                            AND e.event_date = a.attendance_date,
    (
      SELECT
        @occ_ranking := 0,
        @att_ranking := 0,
        @last_member := 0,
        @last_event  := 0,
        @last_missed := 0
    ) v
    ORDER BY
      m.member_id,
      e.event_date
  ) s
) s
WHERE
  is_missed = 1
GROUP BY
  member_id,
  member_name,
  event_id,
  grp
HAVING
  COUNT(*) >= 3
;

Basically, you start with joining members and event_dates to get all the event occurrences the members could have attended based on their membership dates. Then you throw in the attendance table (via a left join) to flag the missed occurrences. Here's an example of what you get by this time:

member_id  event_id  event_date  is_missed
---------  --------  ----------  ---------
1          1         2012-07-10  0
2          1         2012-07-10  1
1          1         2012-07-14  0
2          1         2012-07-14  1
…          …         …           …

At this point, the resulting set needs to be sorted by member_id, event_id, because that is crucial to the subsequent rankings calculation.

Two different rankings are produced for every row. One is a ranking within the row's partition of (member_id, event_id) (it is reset as soon as a new event or a new member is encountered). The other is a ranking within the specific group of consecutive event occurrences, either attended or missed, that the row belongs to (this ranking, in addition to being reset upon coming across a new member or event, also gets reset whenever the other group is encountered). And so you get something like this:

member_id  event_id  event_date  is_missed  ranking1  ranking2
---------  --------  ----------  ---------  --------  --------
…          …         …           …          …         …
1          1         2012-07-27  0          4         4
1          1         2012-07-28  0          5         5
1          1         2012-07-29  1          6         1
2          1         2012-07-10  1          1         1
2          1         2012-07-14  1          2         2
2          1         2012-07-25  1          3         3
2          1         2012-07-27  0          4         1
…          …         …           …          …         …

As you may have noticed, the difference between the two ranking numbers is constant throughout the particular group of consecutive event occurrences of the same kind ("attended" or "missed") and is also unique for that group within its partition. Therefore, every such group can be identified by member_id, event_id and the just mentioned difference.

And it now remains simply to filter the events leaving just the missed ones, group the rows and get the necessary aggregated data, like the number of rows and, possibly, as in the query above, the dates of the first and the last event occurrence in the group. The number of rows is also used in an additional filter condition to omit groups with fewer rows than required.

Postgresql – Select consecutive number of rows starting from some number

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

Best Answer

Related Solutions

Mysql – Count consecutive null rows from a joined table

Postgresql – Select consecutive number of rows starting from some number

Related Question