Is the an SQL squery I can use to fix my issue numbers that have gone out of order?

Similar to a previous question I have asked only this time with minor issue numbers.

I am wondering how I can correct this table:

 +-----------+--------------+-------------+-------------+
 | pkProduct | fkProductID  |intMajorIssue|intMinorIssue|
 +-----------+--------------+-------------+-------------+
 |     1     |     10       |      1      |      0      |
 |     2     |     10       |      2      |      0      |
 |     3     |     10       |      2      |      1      |
 |     4     |     10       |      2      |      1      |
 |     5     |     10       |      2      |      2      |
 |     6     |     11       |      1      |      0      |
 |     7     |     11       |      1      |      1      |
 |     8     |     11       |      1      |      1      |
 |     9     |     11       |      1      |      1      |
 |     10    |     11       |      2      |      0      |
 |     11    |     11       |      2      |      1      |
 |     12    |     12       |      1      |      0      |
 |     13    |     12       |      2      |      1      |
 +-----------+--------------+-------------+-------------+`

To look like this:

 +-----------+--------------+-------------+-------------+
 | pkProduct | fkProductID  |intMajorIssue|intMinorIssue|
 +-----------+--------------+-------------+-------------+
 |     1     |     10       |      1      |      0      |
 |     2     |     10       |      2      |      0      |
 |     3     |     10       |      2      |      1      |
 |     4     |     10       |      2      |      2      |
 |     5     |     10       |      2      |      3      |
 |     6     |     11       |      1      |      0      |
 |     7     |     11       |      1      |      1      |
 |     8     |     11       |      1      |      2      |
 |     9     |     11       |      1      |      3      |
 |     10    |     11       |      2      |      0      |
 |     11    |     11       |      2      |      1      |
 |     12    |     12       |      1      |      0      |
 |     13    |     12       |      2      |      0      |
 +-----------+--------------+-------------+-------------+`

Basically I need to fix the minor issue numbers so that they run in order for each product.

I have been trying to amend the answer I was given on the previous question to do this but so far having no luck.

Any help or advice would be much appreciated!

1 answer

  • answered 2020-05-22 12:46 forpas

    With ROW_NUMBER() window function:

    with cte as (
      select *,
        row_number() over (partition by fkProductID, intMajorIssue order by pkProduct) rn
      from tablename  
    )
    update cte
    set intMinorIssue = rn - 1
    

    See the demo.
    Results:

    > pkProduct | fkProductID | intMajorIssue | intMinorIssue
    > --------: | ----------: | ------------: | ------------:
    >         1 |          10 |             1 |             0
    >         2 |          10 |             2 |             0
    >         3 |          10 |             2 |             1
    >         4 |          10 |             2 |             2
    >         5 |          10 |             2 |             3
    >         6 |          11 |             1 |             0
    >         7 |          11 |             1 |             1
    >         8 |          11 |             1 |             2
    >         9 |          11 |             1 |             3
    >        10 |          11 |             2 |             0
    >        11 |          11 |             2 |             1
    >        12 |          12 |             1 |             0
    >        13 |          12 |             2 |             0