MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table `photo`
(
    `id`       bigint unsigned   not null auto_increment,
    `position` smallint unsigned not null default '0',
    primary key (`id`)
);
INSERT INTO photo (position) VALUES (1), (3), (5), (2), (4);
SELECT * FROM photo;


id
position


1
1

2
3

3
5

4
2

5
4


SET @new_order := '1,2,5,4,3';
UPDATE photo 
SET position = FIND_IN_SET(id, @new_order);
SELECT * FROM photo;


id
position


1
1

2
2

3
5

4
4

5
3