Custom Search
 


Update Statement in MySQL


Update statement is used to make changes to data in one or more columns in a table. The basic UPDATE statement takes the following syntax format:

UPDATE table-name
SET column1 = expression1, column2 = expression2 [,...]
[WHERE criteria]
[ORDER BY...]
[LIMIT row_count]

When creating UPDATE statement, consider the following guidelines.

  1. In the SET clause, you cannot specify a column name more than once.

  2. In the SET clause, the value assigned to a column must be compatible with the column's data type and size attribute.

  3. If you update a column to a NULL value, the column definition must allow NULLs. Otherwise the query will fail.

  4. A lot of the SQL features (e.g. alias, functions, joins, etc.) that we introduced for SELECT statement can be applied to UPDATE statement. There are no extra syntax requirements for them.

  5. The WHERE clause specifies the conditions that identify which rows to update. It's important that you remember to enter a WHERE clause in the update statement unless you are absolutely sure that you don't need one. If you don't enter a WHERE clause, all rows in the SET column(s) will be updated.

  6. If you set a column to the value it currently has (that is, no value change here), MySQL notices this and does not update it. There is a side-effect in PHP if you check the number of rows modified by the update query, because for values that are the same as the values that a column currently has, the number of rows modified is zero.

  7. If the ORDER BY clause is specified, the rows are updated in the order that is specified in the ORDER BY clause. See this tutorial for details.

  8. If the LIMIT clause is specified, it places a limit on the number of rows that can be updated. See this tutorial for details.

Please note that, unlike other DBMS such as SQL Server, Oracle, etc., when using UPDATE, MySQL will not update columns where the new value is the same as the old value, and as such, you may see a message that indicates 0 records are updated even though there are records that match the UPDATE criteria.

Practice #1: Update values in one column.

The following update query increases the UnitPrice by 10% for all products in the Beverages category. In this query, the SET clause is based on the result of a calculation.

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 = 1;

Query result set - 12 rows updated (not all rows are shown in the screenshot)

Practice #2: Update values in two columns.

The following update query increases the UnitPrice of all products in the Beverages category by 10%, and at the same time, increases product reorder level by 5 units for all the Beverage 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 * 1.1, ReorderLevel = ReorderLevel + 5
WHERE CategoryID = 1;

Query result set - 12 rows updated (not all rows are shown in the screenshot)

Happy Coding!



Other tutorials in this category

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


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy