Change MySQL query execution order

I want to put a entry in the database with id of 2. In order to do that I’m moving all records having more than 2 to id + 1.

UPDATE
    `pedo_meta`
SET
    `id` = `id` + 1
WHERE
    `id` > 1;

Because id is a primary key. This query is failing saying :

#1062 - Duplicate entry '3' for key 'PRIMARY'. I know this is happening because query execution starts from top to bottom and when the query tries to make a the column having id 2 to id 3, a duplicate entry error occurs because id 3 column already exists.

I believe this problem can be solved if we reverse the order of execution. So the query starts by making id 537 to 538 then 536 to 537 and so on. So is there any to reverse the order of execution for a query in MySQL?

Answer

You can use ORDER BY on the UPDATE statement to affect the order the rows are updated:

If the ORDER BY clause is specified, the rows are updated in the order that is specified.
source: https://dev.mysql.com/doc/refman/8.0/en/update.html

So you can use the following UPDATE statement:

UPDATE `pedo_meta`
SET `id` = `id` + 1
WHERE `id` > 1
ORDER BY `id` DESC;

demo on dbfiddle.uk

---

Note: If you want to change the sort order of items you shouldn’t use the ID column. You can add an additional numeric column to define a sort order. The ID column should only be use to identify a record in the table.