MySQL Update Statement

UPDATE statement is used for updating existing rows of a table. It has following syntax where values are set for each column to be updated.

UPDATE table_name SET column_name_1 = value_1, column_name_2 = value_2 ... WHERE ... ;

You can run UPDATE statements in MySQL command-line, in a GUI tool or with mysqli_query() in a PHP script.

For this article, we are going to use `employee` table mentioned below. SQL commands for creating the table, inserting data and, truncating are available here.

We recommend truncating the table and re-entering data after each example so that you can identify the change clearly (by comparing with original table shown above).

Result sets listed in this article (except for amending values section) have been generated in that way and by executing SELECT statements (SELECT * FROM `employee`) in command-line.

Updating Rows that Match Conditions

Following is how to update the salary of an employee whose ID is 3.

UPDATE `employee` SET `salary` = 6500 WHERE `id` = 3;

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6500 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

If the company wanted to set the minimum salary to 5500, following query can do the necessary update.

UPDATE `employee` SET `salary` = 5500 WHERE `salary` <= 5500;

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   5500 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5500 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

• When executing above query, three rows become eligible to be updated. But MySQL updates only two rows since one’s value is already 5500.
• MySQL returns 2 (number of rows affected) which will be the return value of mysqli_affected_rows() if you ran the query in a PHP script.

Above query could have been written as below by having < operator in place of <= operator which will select only two rows to be updated.

UPDATE `employee` SET `salary` = 5500 WHERE `salary` < 5500;

You can specify more than one conditions in WHERE clause using AND, OR and IN() as mentioned in article on SELECT statement.

Making Amendments to Existing Values

Sometimes you may not want to replace existing values and only want to make adjustments to existing values. For an example, if you want to increase the salary by 200 of the employee with ID 1, you can use following query.

UPDATE `employee` SET `salary` = `salary`+200 WHERE `id` = 1;

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5700 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  8 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

Following query appends a note to the existing `notes` value after a period and a space. If the current value is NULL, separator is omitted and only the new note is added (it's the behavior of CONCAT_WS() function).

UPDATE `employee` SET `notes` = CONCAT_WS( '. ', `notes`, 'Salary was incremented') WHERE `id` = 1;

SELECT `first_name`, `last_name`, `salary`, `notes` FROM `employee`;

+------------+-----------+--------+------------------------+
| first_name | last_name | salary | notes                  |
+------------+-----------+--------+------------------------+
| Robin      | Jackman   |   5700 | Salary was incremented |
| Taylor     | Edward    |   7200 | NULL                   |
| Vivian     | Dickens   |   6000 | NULL                   |
| Harry      | Clifford  |   6800 | NULL                   |
| Eliza      | Clifford  |   4750 | NULL                   |
| Nancy      | Newman    |   5100 | NULL                   |
| Melinda    | Clifford  |   8500 | NULL                   |
| Harley     | Gilbert   |   8000 | NULL                   |
+------------+-----------+--------+------------------------+

If the column type is numeric, you can use arithmetic operators (+, -, * and /) directly and if it is string, you have to use string functions like CONCAT_WS() and CONCAT().

Setting the Order of Update

MySQL doesn't have a guaranteed order for updates. In cases where order is important, you can specify it like below.

UPDATE `employee` SET `id` = `id`+1 ORDER BY `id` DESC;

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  2 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  3 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  4 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  5 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  6 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  7 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  8 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

In above query, we increment all the IDs by one and it starts incrementing from the last row (8 becomes 9). If it didn't have this order and MySQL tried to do the increment from first row (making 1 into 2), it will throw an error since ID number 2 already exists (`id` has to be unique since it's the primary key).

Limiting Number of Rows to Update

You can make MySQL increment the ID of only the last two rows by specifying a LIMIT clause as below.

UPDATE `employee` SET `id` = `id`+1 ORDER BY `id` DESC LIMIT 2;

+----+------------+-----------+------------------------+--------+-------+
| id | first_name | last_name | job_title              | salary | notes |
+----+------------+-----------+------------------------+--------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | NULL  |
|  8 | Melinda    | Clifford  | Project Manager        |   8500 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | NULL  |
+----+------------+-----------+------------------------+--------+-------+

Using MySQL Update Statement in PHP

Following PHP script shows how to use an update statement in PHP. Two commented out queries correspond to first two conditions of the if-else block. You can try out all three queries by commenting out two queries at a time.

<?php 

/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db'); 

//$query = "UPDATE `employee` SET `salary` = 8000 WHERE `id` = 8";
//$query = "UPDATE `employee` SET `salary` = `salary`+200 WHERE `id` = 7";
$query = "UPDATE `employee` SET `salary` = 5500 WHERE `salary` < 5500"; 

if (mysqli_query($dbConnection, $query)) { 

    $rowsAffected = mysqli_affected_rows($dbConnection);

    if ($rowsAffected == 0) {
        echo "No changes were made";
    } elseif ($rowsAffected == 1) {
        echo "Successfully updated 1 row";
    } elseif ($rowsAffected > 0) {
        echo "Successfully updated $rowsAffected rows";
    }

} else {

    echo "Error occurred: " . mysqli_error($dbConnection);

} 

?>