One of the most common MySQL operation is to change an existing value of a record in a table.
In this article, we’ll explain how to use MySQL update command along with some helpful examples.
The following are covered in this tutorial:
- Update All Rows
- Update Only Selected Rows
- Update Column Value with Expression
- DEFAULT keyword usage in Update
- Update Multiple Columns at the Same Time
- Using LIMIT clause in Update
- Multiple Table Update (using Inner Join)
- Multiple Table Update (Using Left Join)
- Return Updated Value (or Pre-Update Value)
- Combine CASE or IF statement with Update
- Why use ORDER by Clause with Update?
For this tutorial, we’ll use the following employee table as an example. This is the structure of this example table.
mysql> DESC employee; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | dept | varchar(10) | YES | | Sales | | | salary | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
Currently the employee table has the following records.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | | 501 | Ritu | Accounting | NULL | +-----+--------+------------+--------+
If you are new to MySQL, you should probably first understand MySQL basics including how to create MySQL database.
1. Update All Rows
In the following basic example, this update command will set the value of dept column to Technology for all the rows in the employee table.
mysql> UPDATE employee SET dept='Technology'; Query OK, 3 rows affected (0.02 sec) Rows matched: 6 Changed: 3 Warnings: 0
The output of UPDATE command will have the following two lines:
- Line 1: This will say “Query OK” if the query was executed. If there is a syntax error, it will display it here. Even when it didn’t update any record, this line will still say “Query OK” as long as there were no syntax error and the statement was clean. This line will also display how many records were updated by this query (for example: 3 rows affected). Finally, this will also show how long it took for MySQL to execute the query (for example: 0.02 seconds).
- Line 2: This will say how many records where matched by the condition of the update statement. In this example, there is no WHERE condition to restrict the number of records that should be considered for the update (so, it says: Rows matched: 6). Next, this will show how many records were really updated (for example: Changed: 3). Finally, it will display how many warnings where there during update. Pretty much in most cases, you’ll see Warnings as 0 when everything worked properly.
Here are the updated records after the above update command.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Technology | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Technology | 9500 | | 500 | Randy | Technology | 6000 | | 501 | Ritu | Technology | NULL | +-----+--------+------------+--------+
2. Update Only Selected Rows
Instead of updating all the records you can selectively update certain records based on WHERE condition.
The following example will update the employee table and assign all employee who have a salary of greater than or equal to 7000 to Marketing department.
mysql> UPDATE employee SET dept='Marketing' WHERE salary >=7000; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0
There were only two records that matched the above WHERE condition which got updated as shown below.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Technology | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Marketing | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | | 501 | Ritu | Technology | NULL | +-----+--------+------------+--------+
We discussed a lot about the various practical WHERE conditions in our MySQL select command tutorial. It is very helpful to understand how to use the WHERE clause effectively during UPDATE statement
3. Update Column Value with Expression
When you assign a value to a column after the SET, you don’t always have to specify static values. You can also use expressions as shown below.
The following is a very simple expression, where it increments the salary value by 500 for all the employees in Technology department.
mysql> UPDATE employee SET salary=salary+500 WHERE dept='Technology'; Query OK, 3 rows affected (0.01 sec) Rows matched: 4 Changed: 3 Warnings: 0
There were only 4 records that matched the above WHERE condition. But only three records were updated as shown below, as one of the employee records who belongs to Technology department had NULL value in salary field. So, the above salary+500 expression also became NULL, and it didn’t update that particular record.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Technology | 5500 | | 200 | Jason | Technology | 6000 | | 300 | Mayla | Marketing | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6500 | | 501 | Ritu | Technology | NULL | +-----+--------+------------+--------+
4. Update Column Values to DEFAULT
Instead of specifying a static value or an expression, you can also use the keyword “DEFAULT” when you are assigning a value to a column after the SET.
If you look the output of the “DESC employee” shown below, you’ll see there is a column called Default. As you see there, the salary has a DEFAULT value of NULL. The dept has a DEFAULT value of Sales.
mysql> DESC employee; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | dept | varchar(10) | YES | | Sales | | | salary | int(10) | YES | | NULL | | +--------+-------------+------+-----+---------+----------------+
First, let us update the salary column to the default using DEFAULT keyword as shown below.
mysql> UPDATE employee SET salary=DEFAULT; Query OK, 5 rows affected (0.03 sec) Rows matched: 6 Changed: 5 Warnings: 0
Next, update the department column to default value using DEFAULT keyword as shown below.
mysql> UPDATE employee SET dept=DEFAULT; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0
As you see from the following output, we can see that the above DEFAULT keyword took the corresponding default values from the employee table definition, and used them to update it. Department column got updated to Sales and salary updated to NULL as shown below.
mysql> SELECT * FROM employee; +-----+--------+-------+--------+ | id | name | dept | salary | +-----+--------+-------+--------+ | 100 | Thomas | Sales | NULL | | 200 | Jason | Sales | NULL | | 300 | Mayla | Sales | NULL | | 400 | Nisha | Sales | NULL | | 500 | Randy | Sales | NULL | | 501 | Ritu | Sales | NULL | +-----+--------+-------+--------+
5. Update Multiple Columns at the Same Time
In a single update statement, you can also update the values for more than one column as shown below.
In the following example, we are assigning values to both salary and dept column for all the records where the employee id is greater than 300.
mysql> UPDATE employee SET salary=5000, dept='Marketing' WHERE id > 300; Query OK, 3 rows affected (0.04 sec) Rows matched: 3 Changed: 3 Warnings: 0
As you see from the following output, the above update command updated two column values for the last three records which matched the above WHERE condition.
mysql> SELECT * FROM employee; +-----+--------+-----------+--------+ | id | name | dept | salary | +-----+--------+-----------+--------+ | 100 | Thomas | Sales | NULL | | 200 | Jason | Sales | NULL | | 300 | Mayla | Sales | NULL | | 400 | Nisha | Marketing | 5000 | | 500 | Randy | Marketing | 5000 | | 501 | Ritu | Marketing | 5000 | +-----+--------+-----------+--------+
Also, apart from getting a good handle on MySQL update command, it is very helpful to understand all available MySQL INSERT operations
6. Limit How many records to be Updated
We can also use LIMIT option to limit how many records should be updated.
Even if the where condition matched more records, the update statement will update only the 1st X number of records specified by the LIMIT value.
In the following example, we are assigning the salary of all the records to 6500, as we don’t have a where condition. But, we are using LIMIT 3. This means that it will update the salary only the first three records for the matching condition.
mysql> UPDATE employee SET salary=6500 LIMIT 3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0
As you see from the following output, only the 1st three records where updated by the above command.
mysql> SELECT * FROM employee; +-----+--------+-----------+--------+ | id | name | dept | salary | +-----+--------+-----------+--------+ | 100 | Thomas | Sales | 6500 | | 200 | Jason | Sales | 6500 | | 300 | Mayla | Sales | 6500 | | 400 | Nisha | Marketing | 5000 | | 500 | Randy | Marketing | 5000 | | 501 | Ritu | Marketing | 5000 | +-----+--------+-----------+--------+
One thing to keep in mind is that LIMIT 3 doesn’t really mean that update will keep going until it updates 3 records. Instead, the update statement will stop executing as soon as it has processed first 3 rows that matched the where condition irrespective of whether those records were really updated or not.
7. Multiple Table Update (using Inner Join)
You can also combine two tables during update. You can also update values from two tables at the same time using a single update statement.
In this example, we’ll use the following benefits table along with our existing employee table.
mysql> SELECT * from benefits; +------------+-------+ | dept | bonus | +------------+-------+ | Sales | 1000 | | Technology | NULL | | Marketing | 800 | +------------+-------+
The following update statement will increment the value of salary column from employee table to the value specified in the benefits table for the corresponding dept column.
This means that we have to combine both employee and benefits table during the UPDATE as shown below. Use the common field between these two tables in the WHERE clause. In this example, the common field is dept.
Also, right after the UPDATE keyword, specify the name of both the tables as shown below. After the SET keyword you can specify either one or more column name that needs to be updatd from either one table, or both the table.
mysql> UPDATE employee,benefits
-> SET employee.salary=employee.salary+benefits.bonus
-> WHERE employee.dept=benefits.dept and benefits.bonus is not null;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
Please note that in the above update statement we are using inner join.
The following is the output after the above update statement was executed. As you see below, the salary of the employee got incremented based on the values from the above benefits table.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 7500 | | 200 | Jason | Sales | 7500 | | 300 | Mayla | Technology | 6500 | | 400 | Nisha | Technology | 5000 | | 500 | Randy | Marketing | 5800 | | 501 | Ritu | Marketing | 5800 | +-----+--------+------------+--------+
8. Multiple Table Update (Using Left Join)
Similar to the previous example inner join, we can also use left join.
When we are using inner join, we don’t specify the keyword “inner join”, as that is the default while combining multiple tables.
However when using left join, we should explicitly specify “left join” as shown below.
mysql> UPDATE employee LEFT JOIN benefits on employee.dept = benefits.dept
-> SET employee.salary = employee.salary+500
-> WHERE benefits.bonus is null;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Here is the output after the above update.
mysql> SELECT * FROM employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 7500 | | 200 | Jason | Sales | 7500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Technology | 5500 | | 500 | Randy | Marketing | 5800 | | 501 | Ritu | Marketing | 5800 | +-----+--------+------------+--------+
9. Return Updated Value (or Pre-Update Value)
In MySQL, in the update command, there is no direct way of getting the new updates value.
For example, in PostgreSQL, we can use something like this: “UPDATE table_name SET column_name = expression WHERE condition RETURNING column_name. In MySQL, we don’t have the RETURNING concept as part of MySQL update command.
But, you can do the following to get the return value inside your procedure, package, function, or from command line.
mysql> UPDATE employee SET salary = salary+500 WHERE id=400; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT salary AS tmp_salary FROM employee WHERE id=400; +------------+ | tmp_salary | +------------+ | 5500 | +------------+
In the above example, after the update is done, the tmp_salary variable will has the updated salary for employee id
If you like to get the value of the Salary before the update was done, obviously you just have to switch the sequence of the above two statements. In which case, you’ll first the SELECT and then the UPDATE.
Or, you can use “@” as part of the UPDATE statement and get the pre-updated value as shown below.
UPDATE employee SET salary = salary+500 WHERE id=400 AND @tmp_salary := salary
In the above update command, after the update is done, the tmp_salary variable will has the pre-updated salary for employee id 400. As you see here, even though the salary value is already updated to 6000. The tmp_salary variable that was used in the above UPDATE command still has the value of 5500.
mysql> SELECT * FROM employee WHERE id = 400; +-----+-------+------------+--------+ | id | name | dept | salary | +-----+-------+------------+--------+ | 400 | Nisha | Technology | 6000 | +-----+-------+------------+--------+ mysql> SELECT @tmp_salary; +-------------+ | @tmp_salary | +-------------+ | 5500 | +-------------+
10. Combine CASE or IF statement with Update
You can also use conditional updates using MySQL conditional commands like CASE, IF, etc. This is helpful to simplify your updates.
Instead of using multiple updates, you might just be using single UPDATE command that combines all your cases.
For example, let us say we have the following three update statement that updates the salary field based on the value from dept field.
UPDATE employee SET salary = salary+1000 WHERE dept = 'Sales'; UPDATE employee SET salary = salary+500 WHERE dept = 'Technology'; UPDATE employee SET salary = salary+800 WHERE dept = 'Marketing';
You can combine all of the above three UPDATE statement into one single UPDATE statement using the CASE condition as shown below.
UPDATE employee SET salary = CASE dept WHEN 'Sales' THEN salary+1000 WHEN 'Technology' THEN salary+500 WHEN 'Marketing' THEN salary+500 ELSE salary END;
Just like CASE, you can also use IF condition to update the column value accordingly.
11. ORDER By Clause with Update
You can use ORDER BY value during update. ORDER BY clause definitely make sense during SELECT statement. But, why do we need ORDER BY during update.
Let us say you have a UNIQUE id on employee table’s id field.
When you execute the following command to increment the employee ID by 100, you might get an DUPLICATE error message.
mysql> UPDATE contractor set id=id+100; ERROR 1062 (23000): Duplicate entry '200' for key 'PRIMARY'
This is because when it is trying ti update the value of id from 100 to 200, there is already an existing record with id as 200. The ID field also has a UNIQUE constrain, in this case it is the PRIMARY key. So, we are getting the above error.
For this, we have to execute the following command with ORDER BY id desc.
mysql> UPDATE employee SET id=id+100 order by id desc; Query OK, 6 rows affected (0.01 sec) Rows matched: 7 Changed: 7 Warnings: 0
In the above case, it will first start with the maximum employee id, update that record, and then move on to the next. This way, it will never have a scenario of duplicate value during the update process.
No comments:
Post a Comment