MySQL UPDATE Statement

Last update on April 25 2024 08:26:55 (UTC/GMT +8 hours)

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.


-- This SQL statement updates the receive_qty column in the newpurchase table
UPDATE newpurchase 
-- Setting the receive_qty column to the value 20 for all rows
SET receive_qty = 20;

Explanation:

The purpose of this SQL code is to update the receive_qty column in the newpurchase table for all rows, setting it to the value 20.

UPDATE newpurchase: This line specifies the target table where the update operation will be performed, which is newpurchase.

SET receive_qty = 20: This line sets the value of the receive_qty column to 20 for all rows in the newpurchase table.

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.


-- This SQL statement updates the receive_qty column in the newpurchase table
UPDATE newpurchase 
-- Setting the receive_qty column to the value 25 for rows where the purch_price is greater than 50
SET receive_qty = 25 
-- Specifies the condition for which rows to update
WHERE purch_price > 50;

Explanation:

The purpose of this SQL code is to update the receive_qty column in the newpurchase table for rows where the purch_price is greater than 50, setting it to the value 25.

UPDATE newpurchase: This line specifies the target table where the update operation will be performed, which is newpurchase.

SET receive_qty = 25: This line sets the value of the receive_qty column to 25 for the rows that satisfy the specified condition.

WHERE purch_price > 50: This line specifies the condition that determines which rows will be updated. Only rows where the value of the purch_price column is greater than 50 will be affected by this update.

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.


-- This SQL statement updates the receive_qty, pub_lang, and pub_lang columns in the newpurchase table
UPDATE newpurchase 	
-- Setting the receive_qty column to 20, pub_lang column to 'Hindi', and resetting pub_lang to NULL for rows where the purch_price is greater than 50
SET receive_qty = 20, pub_lang = 'Hindi', pub_lang = NULL 
-- Specifies the condition for which rows to update
WHERE purch_price > 50;

Explanation:

The purpose of this SQL code is to update the receive_qty, pub_lang, and pub_lang columns in the newpurchase table for rows where the purch_price is greater than 50.

UPDATE newpurchase: This line specifies the target table where the update operation will be performed, which is newpurchase.

SET receive_qty = 20, pub_lang = 'Hindi', pub_lang = NULL: This line sets the value of the receive_qty column to 20, the pub_lang column to 'Hindi', and then resets the pub_lang column to NULL for the rows that satisfy the specified condition.

WHERE purch_price > 50: This line specifies the condition that determines which rows will be updated. Only rows where the value of the purch_price column is greater than 50 will be affected by this update.

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.


-- This SQL statement updates the receive_qty, pub_lang, and receive_dt columns in the newpurchase table
UPDATE newpurchase 
-- Setting the receive_qty column to 20, pub_lang column to 'Hindi', and receive_dt column to '2008-07-10' for rows where the purch_price is greater than 50
SET receive_qty = 20, pub_lang = 'Hindi', receive_dt = '2008-07-10'
-- Specifies the condition for which rows to update
WHERE purch_price > 50;

Explanation:

The purpose of this SQL code is to update the receive_qty, pub_lang, and receive_dt columns in the newpurchase table for rows where the purch_price is greater than 50.

UPDATE newpurchase: This line specifies the target table where the update operation will be performed, which is newpurchase.

SET receive_qty = 20, pub_lang = 'Hindi', receive_dt = '2008-07-10': This line sets the value of the receive_qty column to 20, the pub_lang column to 'Hindi', and the receive_dt column to '2008-07-10' for the rows that satisfy the specified condition.

WHERE purch_price > 50: This line specifies the condition that determines which rows will be updated. Only rows where the value of the purch_price column is greater than 50 will be affected by this update.

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.


-- This SQL statement updates the purch_price column in the newpurchase table
UPDATE newpurchase 
-- Setting the purch_price column to 5% of its current value for rows where the cate_id is in the result of the subquery
SET purch_price = purch_price * 0.05
-- Specifies the condition for which rows to update
WHERE cate_id IN (
    -- Subquery: Selects cate_id values from the purchase table where the receive_qty is greater than 10
    SELECT cate_id 
    FROM purchase 
    WHERE receive_qty > 10
);

Explanation:

The purpose of this SQL code is to update the purch_price column in the newpurchase table for rows where the cate_id matches values returned from a subquery that selects cate_id values from the purchase table where receive_qty is greater than 10. The purch_price is updated to 5% of its current value.

UPDATE newpurchase: This line specifies the target table where the update operation will be performed, which is newpurchase.

SET purch_price = purch_price * 0.05: This line sets the value of the purch_price column to 5% of its current value for the rows that satisfy the specified condition.

WHERE cate_id IN (SELECT cate_id FROM purchase WHERE receive_qty > 10): This line specifies the condition that determines which rows will be updated. It uses a subquery to select cate_id values from the purchase table where receive_qty is greater than 10, and only rows in newpurchase with cate_id values returned by this subquery will have their purch_price updated.

Subquery Explanation: The subquery selects cate_id values from the purchase table where receive_qty is greater than 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:


-- This SQL statement updates columns val1 and val2 in table1 using conditional logic based on the id column
UPDATE table1 
-- Setting the value of val1 based on the id column, updating it to 5 if id is 1, 8 if id is 3, 7 if id is 4, or keeping its current value otherwise
SET val1 = CASE id 
              WHEN 1 THEN 5 
              WHEN 3 THEN 8 
              WHEN 4 THEN 7 
              ELSE val1
           END, 
-- Setting the value of val2 based on the id column, updating it to 13 if id is 2, 5 if id is 4, or keeping its current value otherwise
    val2 = CASE id 
              WHEN 2 THEN 13 
              WHEN 4 THEN 5 
              ELSE val2 
           END
-- Specifies the condition for which rows to update
WHERE id IN (1, 2, 3, 4);

Explanation:

The purpose of this SQL code is to update the val1 and val2 columns in table1 based on conditional logic applied to the id column.

UPDATE table1: This line specifies the target table where the update operation will be performed, which is table1.

SET val1 = CASE id WHEN 1 THEN 5 WHEN 3 THEN 8 WHEN 4 THEN 7 ELSE val1 END: This line sets the value of the val1 column based on the value of the id column. If the id is 1, val1 will be set to 5; if the id is 3, val1 will be set to 8; if the id is 4, val1 will be set to 7; otherwise, val1 will keep its current value.

SET val2 = CASE id WHEN 2 THEN 13 WHEN 4 THEN 5 ELSE val2 END: This line sets the value of the val2 column based on the value of the id column. If the id is 2, val2 will be set to 13; if the id is 4, val2 will be set to 5; otherwise, val2 will keep its current value.

WHERE id IN (1, 2, 3, 4): This line specifies the condition that determines which rows will be updated. Only rows where the id is either 1, 2, 3, or 4 will be affected by this update.

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 -


-- This SQL statement updates the book_price column in the book_mast table,
-- the purch_price column in the purchase table, and the total_cost column in the purchase table.
UPDATE book_mast, purchase
-- Setting the new value of book_price in the book_mast table to the current value plus 5%.
SET book_mast.book_price = book_mast.book_price + (book_mast.book_price * 0.05),
-- Setting the new value of purch_price in the purchase table to the current value plus 5%.
    purchase.purch_price = purchase.purch_price + (purchase.purch_price * 0.05),
-- Calculating the new value of total_cost in the purchase table based on the updated purch_price and receive_qty values.
    purchase.total_cost = receive_qty * (purchase.purch_price + (purchase.purch_price * 0.05))
-- Specifies the condition for which rows to update.
WHERE book_mast.book_id = purchase.book_id
-- Additional condition: Only update rows where the pub_lang column in the purchase table is "English".
AND purchase.pub_lang = "English";

Explanation:

The purpose of this SQL code is to update the book_price column in the book_mast table, the purch_price column in the purchase table, and the total_cost column in the purchase table for rows where the pub_lang column is "English".

UPDATE book_mast, purchase: This line specifies the target tables where the update operation will be performed, which are book_mast and purchase.
SET book_mast.book_price = book_mast.book_price + (book_mast.book_price * 0.05): This line updates the book_price column in the book_mast table, increasing its value by 5%.

SET purchase.purch_price = purchase.purch_price + (purchase.purch_price * 0.05): This line updates the purch_price column in the purchase table, increasing its value by 5%.

purchase.total_cost = receive_qty * (purchase.purch_price + (purchase.purch_price * 0.05)): This line calculates the new value of the total_cost column in the purchase table based on the updated purch_price and receive_qty values.

WHERE book_mast.book_id = purchase.book_id: This line specifies the condition that determines which rows will be updated. It ensures that only rows with matching book_id values between the book_mast and purchase tables are updated.

AND purchase.pub_lang = "English": This line adds an additional condition, ensuring that only rows where the pub_lang column in the purchase table is "English" will be updated.

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:


-- This SQL statement updates the aval1 column in table11 based on the value of bval1 in table12
-- for rows where the id in table11 matches the id in table13 and bval2 in table12 matches cval1 in table13.
UPDATE table11, table12, table13 
-- Setting the value of aval1 in table11 to the value of bval1 in table12.
SET table11.aval1 = table12.bval1
-- Specifies the condition for which rows to update.
WHERE table11.id = table13.id 
-- Additional condition: Only update rows where bval2 in table12 matches cval1 in table13.
AND table12.bval2 = table13.cval1;

Explanation:

The purpose of this SQL code is to update the aval1 column in table11 based on the values of the bval1 column in table12 for rows where the id in table11 matches the id in table13 and the bval2 in table12 matches the cval1 in table13.

UPDATE table11, table12, table13: This line specifies the target tables where the update operation will be performed, which are table11, table12, and table13.

SET table11.aval1 = table12.bval1: This line updates the aval1 column in table11, setting its value to the value of the bval1 column in table12.

WHERE table11.id = table13.id: This line specifies the condition that determines which rows will be updated. It ensures that only rows where the id in table11 matches the id in table13 will be affected.

AND table12.bval2 = table13.cval1: This line adds an additional condition, ensuring that only rows where the bval2 in table12 matches the cval1 in table13 will be updated.

Explanation

Output:

Previous: INSERT with LEFT JOIN
Next: MySQL Delete