How to make an increment when two fields are unique?

Question

MySQL

There is a table categories (shop_id, index, title)
And there is an index on shop_id and index so that they do not repeat (so that the store does not have the same indexes)
So, I try this query:

update `categories` set `index` = `index` + 1 where `shop_id` = '23eb94c0-1a19-11e8-acea-ade469f32ac7'

That is, I just want to increase the index by 1 for all categories of a particular store
. But the database gives the following:

#1062 - Дублирующаяся запись '1-23eb94c0-1a19-11e8-acea-ade469f32ac7' по ключу 'categories_index_shop_id_unique'

That is, he says that not unique bundles of fields are obtained,
BUT at the same time, if you do not increment, but rather subtract one, then everything works:

update `categories` set `index` = `index` - 1 where `shop_id` = '23eb94c0-1a19-11e8-acea-ade469f32ac7'

How can this be fixed?

Answer 1

Try updating by column value in reverse order: ORDER BY index DESC.