Verification: a143cc29221c9be0

Php and mysql update query

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  |
+----+------------+-----------+------------------------+--------+-------+

It’s optional to have a WHERE clause in UPDATE statements. But not having it can lead to critical errors. For an example, in above query, if WHERE clause was omitted, salary will be set to 6500 for all the employees.

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` 
+----+------------+-----------+------------------------+--------+-------+
| 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

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

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  |
+----+------------+-----------+------------------------+--------+-------+