By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.5.13-MariaDB |
CREATE TABLE mytable (
product_key INTEGER,
image_key INTEGER,
image_order INTEGER,
ordered_id INTEGER);
INSERT INTO mytable(ordered_id,product_key, image_key, image_order) VALUES
(16384,5692,42265,0),
(17648,5692,42207,1),
(18859,5692,42210,2),
(20070,5692,42212,3),
(22376,5692,42266,5),
(23640,5692,42273,6),
(24904,5692,42268,7),
(26168,5692,42264,8);
SELECT * FROM mytable;
| product_key | image_key | image_order | ordered_id |
|---|---|---|---|
| 5692 | 42265 | 0 | 16384 |
| 5692 | 42207 | 1 | 17648 |
| 5692 | 42210 | 2 | 18859 |
| 5692 | 42212 | 3 | 20070 |
| 5692 | 42266 | 5 | 22376 |
| 5692 | 42273 | 6 | 23640 |
| 5692 | 42268 | 7 | 24904 |
| 5692 | 42264 | 8 | 26168 |
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;
SELECT * FROM mytable;
| product_key | image_key | image_order | ordered_id |
|---|---|---|---|
| 5692 | 42265 | 0 | 16384 |
| 5692 | 42207 | 1 | 17648 |
| 5692 | 42210 | 2 | 18859 |
| 5692 | 42212 | 3 | 20070 |
| 5692 | 42266 | 5 | 22376 |
| 5692 | 42273 | 6 | 23640 |
| 5692 | 42268 | 7 | 24904 |
| 5692 | 42264 | 8 | 26168 |