MySQL Update Statement Tutorial – Update Query Syntax & Examples

This Tutorial Explains the MySQL UPDATE Statement Along with Query Syntax & Examples. You will Also Learn Different Variations of MySQL Update Table Command:

As with any other database, we always have a need to update or modify or change existing data in the tables. In MySQL, we have the UPDATE statement that could be used to update or modify the data in the table.

Using this command, we can update one or many fields. We can update the values of a particular table at a time. By using the WHERE clause we can specify the conditions used especially when there is a need to update specific rows from a table.

=> Click here for the complete MySQL tutorial series

Before proceeding ahead, please note, that we are using MySQL version 8.0. You can download it from here.

MySQL UPDATE Table Syntax

UPDATE table_name SET column1 = new_value1, column2 = new_value2, ...
        WHERE condition;

Syntax Explanation:

• The syntax starts with the keyword “UPDATE”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted.
• Next comes the name of the table on which the update action has to be performed. This is mandatory and cannot be omitted.
• Third, is again a keyword – SET. This keyword informs MySQL Server about the values to be updated for the column names. This is a mandatory keyword and cannot be omitted.
• Next, will be the column names to be updated along with their corresponding values. This is also mandatory and cannot be omitted.
• Then comes the WHERE condition, which restricts or filters the number of target rows on which the UPDATE action has to be applied. WHERE is also a keyword, but an optional one.

The WHERE clause is, however, significant. If not mentioned, or if the condition is not set correctly then neither the table nor the non-required rows will get updated.

Modifiers In An UPDATE Table Statement

Enlisted below are the modifiers in an UPDATE statement.

LOW_PRIORITY: This modifier informs the MySQL Engine to delay the update until there is no connection reading from the table.

IGNORE: This modifier informs MySQL Engine to continue with the UPDATE operation even if there are any errors. No update action is performed on the rows that caused errors.

MySQL UPDATE Example

Given below is a sample table created in MySQL.

Schema Name: pacific
Table Name: employees
Column Names:

• empNum – Holds integer values for the employee number.
• lastName – Holds varchar values for the last name of the employee.
• firstName – Holds varchar values for the first name of the employee.
• email – Holds varchar values for the email ID of the employee.
• deptNum – Holds varchar for the department ID that an employee belongs to.
• salary – Holds decimal values of salary for each employee.

Schema Name: pacific
Table Name: departments
Column Names:

• deptNum – Holds varchar for department ID within an organization.
• city – Holds the name of the city in which the departments work from.
• country – Holds the name of the country corresponding to the city.
• bonus – Holds the percentage value of the bonus.

MySQL UPDATE Table Command

#1) MySQL Updating Single Column

Now, let’s find out a record that we would want to update. First, we will have a look at a scenario where we have to update a single column using the UPDATE keyword.

Here is an employee with the employee number as 1008.

The query and its corresponding results are as follows:

Let’s update the email ID of this employee from ob@gmail.com to oliver.bailey@gmail.com, using the UPDATE keyword.

UPDATE: The keyword informs the MySQL engine that the statement is about Updating a table.
SET: This clause sets the value of the column name mentioned after this keyword to a new value.
WHERE: This clause specifies the particular row that has to be updated.

Post executing the UPDATE statement, the output will show the statistics related to the statement execution.

Following are the details that are shown:

• A statement that was executed.
• Messages that show the number of rows that were updated and if there were any warnings.

In order to verify the output of the UPDATE statement, let’s re-execute the SELECT statement to see the change in the email ID.

Table Snapshot Before:

Query:

UPDATE employees
   SET email = “oliver.bailey@gmail.com”
 WHERE empNum = 1008
   AND email = “ob@gmail.com”
;

Table Snapshot After:

#2) MySQL Update Multiple Columns

The syntax to update more than one column using the UPDATE statement is the same as that of updating a single column. One single SET statement will have multiple column names along with its new value that has to be set, separated by a comma.

Let’s have a look at the row that we need to update. Row with the employee number as 1003.

Here, we will try and update the lastName from “Mary” to “Margaret” and then the email ID from ml@gmail.com to margaret.langaley@gmail.com.

The following is the UPDATE query. Do observe the column names separated by a comma.

The output of the above execution shows the same statistics as in the previous case.

Following is the output for the same record post the execution of the UPDATE statement.

Table Snapshot Before:

Query:

UPDATE employees
   SET firstName = “Margaret”,
	 email = “margaret.lagaley@gmail.com”
 WHERE empNum = 1003
   AND firstName = “Mary”
   AND email = “ml@gmail.com”
;

Table Snapshot After:

#3) MySQL Update With REPLACE Function

Let’s see more about using the REPLACE function to UPDATE a row in the table. Here is our target record that we want to update.

The below record is for employee number 1010. We will target to update the email ID from ja@gmail.com to jacob.armstrong@gmail.com.

Let’s use the following UPDATE query with the REPLACE function that will update the email ID.

The following are the parameters that are passed in the REPLACE function. All 3 parameters are positional in nature i.e. the order of the parameters cannot be altered.

1^st Parameter – Contains the name of the email ID.
2^nd Parameter – Contains the FROM email ID that is to be changed.
3^rd Parameter – Contains the TO email ID which is the new value.

Following is the snapshot of the table post-execution of the UPDATE statement:

Table Snapshot Before:

Query:

UPDATE employees
   SET email = REPLACE(email, “ja@gmail.com”, jacob.armstrong@gmail.com)
 WHERE empNum = 1010
;

Table Snapshot After:

#4) MySQL UPDATE Using SELECT Statement

In this type of UPDATE, the new value for the column to be updated is fetched by a SELECT statement in a subquery. So, let’s take an example here from our “employees” table. Here is our target record that we want to update.

In this case, we will update the department number i.e. deptNum column, using the departments tables. If we look at the departments table, the deptNum = 5 corresponds to Berlin. Let’s move this employee to Charlotte at deptNum = 2.

In order to achieve this task, the following UPDATE statement is used:

In order to verify the output of our UPDATE statement, let’s execute the SELECT statement.

As shown above, the value for the deptNum column has been updated to “2”.

Table Snapshot Before:

Query:

<UPDATE employees
   SET deptNum = (SELECT deptNum
	            FROM departments
                    WHERE city = "Charlotte"
		 )
   WHERE empNum = 1005
 ;

Table Snapshot After:

#5) MySQL UPDATE Multiple Rows

At times, we might face a requirement where we have to update one or more columns for multiple rows with different values.

For Example, we want to give a particular amount of bonus department wise i.e. all employees in a department should get a particular amount of bonus.

The general syntax is as follows:

 UPDATE TAB1 
       SET COL2 = CASE 
		  WHEN condition1 THEN value1 
		  WHEN condition2 THEN value2 
		  …. 
		  ELSE result1 
       END; 

To explain this with an example lets add one more column to the department tables. We will add the “bonus” column to the department table. The idea is to assign a bonus percentage to each department and hike the salary of the employees by that percentage corresponding to each department.

To achieve this, we will execute the following ALTER statements to add a column:

ALTER TABLE departments ADD COLUMN bonus decimal(5,2);

The following would be the table structure post the above changes. The new columns will be added with NULL as value.

Next, let’s write the UPDATE query that will update the bonus percentage for each department.

Post execution of the above statement, the following is the snapshot with the updated values for the Bonus column.

Table Snapshot Before:

Query:

UPDATE departments
   SET bonus = CASE
	              WHEN deptNum = 1 THEN 3.00
                      WHEN deptNum= 2 THEN 5.00
                      WHEN deptNum= 3 THEN 8.00
                      WHEN deptNum= 4 THEN 10.00
                      WHEN deptNum= 5 THEN 13.00
                      WHEN deptNum= 6 THEN 15.00
                      WHEN deptNum= 7 THEN 18.00
   END;

Table Snapshot After:

#6) MySQL UPDATE Using INNER JOIN Keyword

JOIN is one of the most important keywords in the SQL statements. Usually, you might have used it in the SELECT statement.

There are basically four types of JOIN statements:

• INNER JOIN: Fetches the records that are common in both tables.
• LEFT JOIN: Fetches all records from the table on the left side of the keyword and the matching records from the table on the right side of the keyword.
• RIGHT JOIN: Fetches all records from the table on the right side of the keyword and the matching records from the table on the left side of the keyword.
• OUTER JOIN: Fetches all records from both the tables, with the corresponding mismatched records represented as NULL.

MySQL gives a unique opportunity to use JOIN even in UPDATE statements to perform cross-table updates. However, it’s limited only to INNER JOIN and LEFT JOIN.

The generic syntax of UPDATE statement using the JOIN keyword is as follows:

UPDATE TAB1, TAB2,
     [INNER JOIN | LEFT JOIN] TAB1 ON TAB1.COL1 = TAB2.COL1
     SET TAB1.COL2 = TAB2.COL2,
                     TAB2.COL3 = expr
     WHERE condition

• Here, the UPDATE statement expects three data items.
• Table names, TAB1 and TAB2, on which join is being performed.
• Type of JOIN that we intend to perform, INNER or LEFT.
• Then follows the SET command using which we can update the column values in either/or TAB1 and TAB2.
• Lastly, a WHERE clause to update only those rows that fit our criteria.

To explain this with an example lets add one more column to the Employees table. We will add the “salary” column to the Employees table. The idea is to hike the salary of employees by a bonus percentage value present in the bonus column of the department table.

To achieve this, we will execute the following ALTER statements to add a column:

ALTER TABLE employees ADD COLUMN salarydecimal(7,2);

Next, we will populate the two new fields that we have added. Post populating the values, the following is the content of the table.

Employees Table:

Now, let’s use the JOIN keyword and update the salary of all the employees with a bonus percentage in the departments’ table. Here, deptNum is the key on which the two tables will be matched.

Following is the snapshot of the salaries of employees as of now:

Snapshot from Departments table is as follows:

Following is the UPDATE query that will update the salary of the employees based on the bonus percentage in the departments’ tables based on the deptNum key column.

Now, let’s verify the salary of each employee post-hike.

If you compare it with the previous snapshot, then you can easily understand the bonus percentage added to the salary.

All employees must be cheering!

Table Snapshot Before:

Query:

 
UPDATE employees 
 INNER JOIN departments 
    ON employees.deptNum = departments.deptNum 
   SET 
       salary = salary + ((salary * bonus)/100) ; 

Table Snapshot After:

#7) MySQL UPDATE Using LEFT JOIN Keyword

As explained in the previous section, there are two types of JOIN that are allowed in MySQL UPDATE. We have already seen UPDATE using INNER JOIN.

Let’s start with UPDATE using LEFT JOIN.

Example:

We have a new hire who is yet to be assigned to any department. But we have to give all new hires a bonus of 1%. Now, as the new hire is not assigned to any department, we won’t be able to get any bonus percentage information from that table. In such a case, we will UPDATE the salary for the new hires using LEFT JOIN.

To achieve this, let’s add a new employee to the employee database.

INSERT INTO employees(empNum, firstName, lastName, email, deptNum, Salary)
VALUES (1011, “Tom”, “Hanks”, th@gmail.com, NULL, 10000.00);

Following is the new record that we have added:

Employees Table:

Next, we will give Tom a bonus of 1% on top of his salary using the UPDATE statement with LEFT JOIN clause:

Given below is the salary of TOM post-hike.

If you compare it with the previous snapshot, you can easily understand the bonus % added to the salary.

Table Snapshot Before:

Query:

 
UPDATE employees 
      LEFT JOIN departments 
          ON employees.deptNum = departments.deptNum 
         SET salary = salary + ((salary * 1)/100) 
 WHERE employees.deptNum IS NULL 
; 

Table Snapshot After:

Recommended Reading =>> MySQL Joins

Frequently Asked Questions And Answers

Q #1) How do I update attributes in MySQL?

Answer: We can update attribute(s) using MySQL UPDATE statement, with the statement beginning with the UPDATE keyword followed by the table name. Next is the SET clause followed by a column name and a WHERE clause.

Q #2) How do you update multiple records in MySQL?

Answer: As depicted above, under the “Update Multiple Rows” section, we can update multiple rows for one or more columns with the same or different values using the CASE statement.

Q #3) Can we use JOIN in Update query in MySQL?

Answer: Yes, MySQL allows using JOIN in UPDATE statements. However, it’s restricted only to INNER and LEFT JOIN.

Q #4) Where to download MySQL from?

Answer: You can download MySQL version 8.0 from here: MySQL.

Q #5) What is the significance of the WHERE clause in the Update statement?

Answer: WHERE clause restricts the number of rows that should fit in the UPDATE clause’s criteria.

Suggested reading =>> How to use the MySQL If Statement 

Conclusion

Thus in this tutorial, we have learned about 7 different ways of executing MySQL UPDATE statements.

1. Update a single column
2. Update multiple columns
3. Update using REPLACE
4. Update using SELECT
5. Update multiple rows
6. Update using INNER JOIN
7. Update using LEFT JOIN

We can use either of these, based on our requirements.

Happy Reading!!