Change MySQL query execution order

user_10362474 0
Viewed 123

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?

mysql sql
2 Answers

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.

user_38408400

Here is a simple algorithm to accomplish that fast:
1. create a new column new_id
2. set new_id = id + 1
3. update id and set it to be id = new_id
4. remove column new_id when you are done

P.S. IF you want to sort your items after id is better to create a new column sort_order which can be updated extremely fast...

user_30828530
Related