Practice #1: Update top 2 rows.

The following update query increases the UnitPrice by 10% for the first two products in the Condiments category (ordered by ProductID).

To instruct MySQL how to pick the products for update, we use ORDER BY clause. To update only 2 rows, we use LIMIT clause. In this query, the data is sorted by ProductID in ascending order and the first 2 products are chosen by the LIMIT 2 clause.

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

UPDATE products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
ORDER BY ProductID
LIMIT 2;

Before update - the screenshot below shows that the UnitPrice for the two products are $10 and $22 (for CategoryID 2 only - Condiments).

After update - 2 rows updated. Now the UnitPrice for the two products are increased by 10% to $11 and $24.2

Practice #2: Update UnitPrice for the top 5 most expensive products.

The following update query reduces the UnitPrice by 10% for the top 5 most expensive products.

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

UPDATE products
SET UnitPrice = UnitPrice * 0.9
ORDER BY UnitPrice DESC
LIMIT 5;

Before update - the screenshot below shows the UnitPrice for the top 5 most expensive products.

After update - 5 rows updated. UnitPrice is reduced by 10% for the top 5 most expensive products.

Happy Coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order 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