How to update data in a specific order

How to update data in a specific order in MySQL

The update statement in MySQL supports the use of ORDER BY clause to specify the order of update. In this tutorial, we'll see how this is useful in updating the ID column in Order_Details table in Northwind database.

Practice: Update data in a specific order.

The following update query increments the ID value in Order_Details table by 1. The update is possible because Order_Details table is not referenced by any other tables in the Northwind database. That is, ID is not referenced by a foreign key in other table(s). For more information about foreign key, refer to tutorial Foreign Key Relationships and Considerations.

The most important syntax in the query below is the DESC keyword (descending order) in the ORDER BY clause, which instructs MySQL to start updating from the last ID value 2155. It must be done this way because ID is a primary key column and so no duplicate values are allowed. When ID 2155 is updated to 2156, MySQL checks to see if 2156 already exists in the table, because it does not exist so the update is successful and continues to update the next ID value 2154. The pattern is that rows with larger ID values are updated before those with smaller ID values. It goes in this fashion until ID 1 is updated to 2.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

UPDATE order_details
SET ID = ID + 1
ORDER BY ID DESC;

Before update - the screenshot below shows the last 12 ID values in Order_Details table.

Before updating ID in Order_Details table.

After update - 2155 rows updated. Now each of the last 12 ID values has been incremented by 1.

After updating ID in Order_Details table.

Happy Coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update top N rows of data in MySQL

3. Use outer join in update

4. How to do cross-table update in MySQL - Part 1

5. How to do cross-table update in MySQL - Part 2

6. Using Bulk Insert Statement

7. Basic insert statement

8. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

9. Combine update and insert into one statement

10. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

11. Update multiple rows (distinctive by primary key) with different values in one query

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page

	Custom Search