MySQL Fix Gap in Ordinal Sequence

Question

all. I'm working on an assignment to update the order images appear in a table. This is done with a table structure that looks like: product_key, image_key, image_order. The first two are foreign keys to other tables and the latter is an ordinal sequence in which the images will appear. In the process of selecting the images, I matched those available to a set of rules. In some cases there was no image that fit a criteria set leading to a gap in the image order. E.g.,

product_key	image_key	image_order
5692	42265	0
5692	42207	1
5692	42210	2
5692	42212	3
5692	42266	5
5692	42273	6
5692	42268	7
5692	42264	8

This is an example for a single product_key but there are over 1200 product_keys and over 11k total rows. What I need to do is find a place where row(n) product_key equals row(n-1) product_key but row(n) image_order does not equal row(n-1) image_order+1 and then change row(n) image order to row(n-1) image_order+1.

Based on an example from someone who no longer works here, I tried this:

UPDATE mytable i
    JOIN (
        SELECT ordered_id, product_key, image_order, 
            (@ROW:=(IF(@productkey=product_key, @ROW+1, 0))) AS row_order, 
            (@productkey:=product_key) AS pid 
            FROM mytable,
                (SELECT @ROW=-1, @productkey:=-1) a 
                ORDER BY product_key, image_order) b 
            ON b.ordered_id=i.ordered_id
        SET i.image_order = b.row_order
        WHERE i.image_order <> b.row_order;

But that updated image_order to 0 in all rows due to some difference between when he developed it to when I ran it.

I am looking at this example and I feel like it's close to what I need, but I haven't figured it out yet.

Here's the DBFiddle for the problem.

Any thoughts on how to cover those gaps so the final display doesn't have gaps?

TIA

Answer 1

Many thanks to @luuk and @sos! Here is the solution:

insert into mytable_2 
with image_order_update_CTE (ordered_id, product_key, image, image_order, R)
as 
(
SELECT 
   m.*, 
   row_number() over (partition by product_key order by image_order)-1 R 
FROM mytable m
ORDER BY product_key
)
select image_order_update_CTE.ordered_id, image_order_update_CTE.product_key, image_order_update_CTE.image, image_order_update_CTE.image_order, image_order_update_CTE.R from image_order_update_CTE
;

I really appreciate this community and thanks again. VtR