MySQL UPDATE Statement
UPDATE Table
The MySQL UPDATE statement is used to update columns of existing rows in a table with new values.
Version: 5.6
Syntax :
Single table:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
Multiple tables:
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]
Arguments
Name | Description |
---|---|
table_reference(s) | Name of table(s) to be updated. |
col_name1, col_name2, .. | Name of column(s) to be updated. |
expr1, expr2,... | New value(s). |
- For a single table, the UPDATE statement updates columns of existing rows in the named table with new values. Specific columns can be modified using the SET clause by supplying new values for that column.
- The WHERE clause can be used to specify the conditions those identify which rows to update. Without using WHERE clause, all rows are updated.
- The ORDER BY clause is used to update the order that is already specified.
- The LIMIT clause specifies a limit on the number of rows that can be updated.
- For multiple tables, UPDATE updates row in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.
The UPDATE statement supports the following modifiers:
- LOW_PRIORITY: Using LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).
- IGNORE : Using IGNORE keyword, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are updated to values that would cause data conversion errors are updated to the closest valid values instead.
Following are some examples on MySQL update where we have used newpurchase as sample table.
Sample table: newpurchase
MySQL UPDATE column
MySQL UPDATE column can be used to update some specific columns. The following MySQL statement will update the 'receive_qty' column of newpurchase table with a new value 20.
UPDATE newpurchase SET receive_qty=20;
MySQL UPDATE with WHERE
MySQL UPDATE command can be used with WHERE clause to filter (against certain conditions) which rows will be updated. The following MySQL statement will update the 'receive_qty' column of newpurchase table with a new value 25 if the value of purch_price is more than 50.
UPDATE newpurchase
SET receive_qty=25
WHERE purch_price>50;
MySQL UPDATE using NULL
MySQL UPDATE command can be used to update a column value to NULL by setting column_name = NULL, where column_name is the name of the column to be updated. The following MySQL statement will update pub_lang column with NULL if purch_price is more than 50. In this statement, other columns are also updated with respective new values.
UPDATE newpurchase
SET receive_qty=20,pub_lang='Hindi',pub_lang=NULL
WHERE purch_price>50;
MySQL UPDATE multiple columns
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated. The following MySQL statement will update receive_qty, pub_lang, and receive_dt columns with new values 20, Hindi and 2008-07-10 if purch_price is more than 50.
UPDATE newpurchase
SET receive_qty=20,pub_lang='Hindi',receive_dt='2008-07-10'
WHERE purch_price>50;
MySQL UPDATE with subqueries
Here in the following, we have discussed how to use MySQL UPDATE command with subqueries.
The following MySQL statement will update purch_price with purch_price multiplied by 5 if it satisfies the condition defined in the subquery started with SELECT wrapped within a pair of parenthesis.
The subquery retrieves only those cate_ids from purchase table if their corresponding receive_qty is more than 10.
UPDATE newpurchase
SET purch_price=purch_price*.05
WHERE cate_id IN(SELECT cate_id
FROM purchase
WHERE receive_qty>10);
Updating MySQL Table using PHP Script
You can update MySQL table data (using UPDATE command) through a PHP script. Within the script, PHP function MySQL_query() execute the SQL command. We have used a table called 'item' to apply the query:
Table Name : item Structure : item_code varchar(20), value int(11), quantity int(11) where item_code is the primary key. In the following rows of item table, 'value' column which is marked with red rectangle will be updated.

PHP Script
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$connec = MySQL_connect($dbhost, $dbuser, $dbpass);
if(!$connec)
{
die('Could not connect: ' . MySQL_error());
}
$sql = "UPDATE item
SET value = '112'
WHERE item_code='item1'";
MySQL_select_db('MySQL');
$result = MySQL_query($sql, $connec);
if(!$result)
{
die('Could not update data: ' . MySQL_error());
}
echo "Data successfully updated...";
MySQL_close($connec);
?>
Sample Output:

Multiple Updates in MySQL
Sample table: table1

Problem
If you want to update the val1 with 5,8 and 7 for concerned id 1,3 and 4 and the other val1 will remain same and the val2 will be updated with 13 and 5 for the concerned id 2 and 4 and the other will remain same, the following update statement can be used by using IF and CASE.
Code:
UPDATE table1 SET val1= CASE id
WHEN 1 THEN 5
WHEN 3 THEN 8
WHEN 4 THEN 7
ELSE val1
END,
val2= CASE id
WHEN 2 THEN 13
WHEN 4 THEN 5
ELSE val2
END
WHERE id IN (1, 2, 3, 4);
Pictorial presentation:

Sample Output:

Examples: MySQL UPDATE on multiple tables
Here we have used two tables book_mast and purchase for the following example as sample table. We have shown some of the columns in the associated tables. Here are the tables below -
MySQL> SELECT book_id,book_name,pub_lang,book_price -> FROM book_mast; +--------+------------------------------------+---------+----------+ | book_id| book_name | pub_lang|book_price| +--------+------------------------------------+---------+----------+ | BK001 | Introduction to Electrodynamics | English | 85.00| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 250.00| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| +--------+------------------------------------+---------+----------+ 16 rows in set (0.00 sec) and MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> FROM purchase; +---------+----------+-------------+------------+ | book_id | pub_lang | purch_price | total_cost | +---------+----------+-------------+------------+ | BK001 | English | 75.00 | 1125.00 | | BK004 | English | 55.00 | 440.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 35.00 | 525.00 | | BK001 | English | 25.00 | 200.00 | | BK003 | Hindi | 45.00 | 900.00 | +---------+----------+-------------+------------+ 6 rows in set (0.02 sec)
If we want to update the book_price of the table book_mast by an increment of 5% and also update the purch_price and total_cost of puchase table by an increment of 5%, and this increment will affect only those rows in both book_mast and purchase table, which publishing language is English and book_id matching in both the tables, we can write the following code -
UPDATE book_mast,purchase
SET book_mast.book_price=book_mast.book_price+(book_mast.book_price*.05),
purchase.purch_price=purchase.purch_price+(purchase.purch_price*.05),
purchase.total_cost=receive_qty*(purchase.purch_price+(purchase.purch_price*.05))
WHERE book_mast.book_id=purchase.book_id
AND purchase.pub_lang="English";
After updating it is to be shown that, the highlighted rows have been effected in both the tables.
MySQL> SELECT book_id,book_name,pub_lang,book_price -> FROM book_mast; +--------+------------------------------------+---------+----------+ | book_id| book_name | pub_lang|book_price| +--------+------------------------------------+---------+----------+ | BK001 | Introduction to Electrodynamics | English | 89.25| | BK002 | Understanding of Steel Construction| English | 105.50| | BK003 | Guide to Networking | Hindi | 200.00| | BK004 | Transfer of Heat and Mass | English | 262.50| | BK005 | Conceptual Physics | NULL | 145.00| | BK006 | Fundamentals of Heat | German | 112.00| | BK007 | Advanced 3d Graphics | Hindi | 56.00| | BK008 | Human Anatomy | German | 50.50| | BK009 | Mental Health Nursing | English | 145.00| | BK010 | Fundamentals of Thermodynamics | English | 225.00| | BK011 | The Experimental Analysis of Cat | French | 95.00| | BK012 | The Nature of World | English | 88.00| | BK013 | Environment a Sustainable Future | German | 100.00| | BK014 | Concepts in Health | NULL | 180.00| | BK015 | Anatomy & Physiology | Hindi | 135.00| | BK016 | Networks and Telecommunications | French | 45.00| +--------+------------------------------------+---------+----------+ 16 rows in set (0.01 sec) and MySQL> SELECT book_id,pub_lang,purch_price,total_cost -> FROM purchase; +---------+----------+-------------+------------+ | book_id | pub_lang | purch_price | total_cost | +---------+----------+-------------+------------+ | BK001 | English | 78.75 | 1181.25 | | BK004 | English | 57.75 | 462.00 | | BK005 | NULL | 20.00 | 400.00 | | BK004 | English | 36.75 | 551.25 | | BK001 | English | 26.25 | 210.00 | | BK003 | Hindi | 45.00 | 900.00 | +---------+----------+-------------+------------+ 6 rows in set (0.08 sec)
MySQL: Update with Join Statement
Sample tables

Problem
If we want to update the aval1of table11 with the bval1 of table12 against the following condition -
1). the id of table11 and table13 must be matched, and
2). bval2 of table12 must be matched with the cval1 of table13 -
then the following code can be used.
Code:
UPDATE table11, table12, table13
SET table11.aval1 = table12.bval1
WHERE table11.id = table13.id
AND table12.bval2 = table13.cval1
Explanation

Output:

Previous:
INSERT with LEFT JOIN
Next:
MySQL Delete
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook