Update Query in MySQL

Introduction

Suppose, we are working on Tables in MySQL Database and a requirement came up that will require changes in a certain table of the Database. In that case, we can utilize the Update query in MySQL to take care of our needs.

In MySQL, the UPDATE statement is a powerful tool that enables us to make changes to large amounts of data with a single query. Depending on our requirements, the UPDATE statement allows us to change one or more columns in a table for a single record or for numerous records at once. we can define which records to update based on particular criteria by using the WHERE clause.

Syntax

The basic syntax of the MySQL UPDATE statement is as follows:

The table_name option in this syntax gives the name of the table that we want to modify. The columns we want to edit and their new values must be specified using the SET keyword. By separating the columns with commas, update one or more of them at once. We can select the rows that we want to update using the optional WHERE clause. The whole table will be updated if the WHERE clause is not included.

Parameters

Below are the description of each parameter used in the UPDATE statement :

Examples

Let's look at some examples of how to update data in a table using the MySQL UPDATE command.

Update Single Column

Let's see a basic example of how to use the MySQL update query to update a specific column.

we have a table employees containing the following data:

Example 1: Suppose, we want to increase the salary for employee id = 1 to 55000. Here's what the below query does:

• UPDATE employees -
  This specifies the table we want to update, which in this case is the employees table.
• SET salary = 55000 -
  This specifies the column we want to update (salary) and the new value we want to set for it (55000).
• WHERE id = 1 -
  This specifies the condition that must be met for the update to occur, which in this case is that the id column must be equal to 1.

When we run this query, MySQL will find the employee record with the id=1 and change the salary field to 55000. The table's other records will not be modified.

Query:

Output:

In the output table, we can see that the salary of employee with id=1 has been increased to 55000.

Example 2:
Suppose, we want to increase the salary for all the employee by 5000. Here's what the below query does:

• UPDATE employees -
  This specifies the table we want to update, which in this case is the employees table.
• SET salary = salary + 5000 -
  This specifies the column we want to update (salary) and the new value we want to set for it (salary+5000).

Query:

Output:

In the output table, we can see that the salary of all the employees has been increased by 5000.

Update Multiple Columns

Let's see a basic example of how to use the MySQL update query to update mulitple number of columns.

we will be using the same employees containing the following data:

Example 3:
Suppose, we want to increase the salary and change the lastname for employee with id = 1. Here's what the below query does:

• UPDATE employees -
  This specifies the table we want to update, which in this case is the employees table.
• SET salary = 55000, last_name='Miller' -
  This specifies the columns we want to update (salary, last_name) and the new value we want to set for the columns (55000, "Miller").
• WHERE id = 1 -
  This specifies the condition that must be met for the update to occur, which in this case is that the id column must be equal to 1.

When we run this query, MySQL will find the employee record with the id=1 and change the salary field to 55000 and last_name to 'Miller'. The table's other records will not be modified.

Query:

Output:

In the output table, we can see that the salary of employee with id=1 has been increased to 55000 and last_name has been changed to "Miller".

Update Using WHERE Clause

This code is an example of how to use the WHERE Clause to update the data of the Table.

Example 4:
Suppose, we want to increase the salary of all the employees. Here's what the below query does:

• UPDATE employees -
  This specifies the table we want to update, which in this case is the employees table.
• SET salary = salary+10000 -
  This specifies the columns we want to update (salary) and the new value we want to set for the columns.
• salary>60000 -
  This specifies the condition that must be met for the update to occur, which in this case is that the salary column must be greater than 60000.

Output :

In the output table, we can see that the salary of employees with salary greater than 60000 has been increased by 10000.

Update using JOINS

We can also update the Table using JOINS. For that we will be using 2 tables and will be joining them and updating the values:

Employee Table:

Employee Locations Table:

Example 5:
Lets breakdown the query for joining these tables:

• UPDATE employees -
  This specifies the table we want to update, which in this case is the employees table.
• INNER JOIN emp_location ON employees.id = emp_location.id -
  This line makes an inner join with the emp_location table using the id column as the join condition. This means that only rows where employees.id matches emp_location.id will be updated.
• SET employees.salary = employees.salary * 1.1 -
  This line sets the salary column in the employees table to be 10% higher than its current value.
• WHERE emp_location.location = 'New York' -
  This line specifies the condition for which rows to update. In this case, it's all employees who have a location value of 'New York' in the emp_location table.

Output :

This update query will update the salary of all employees who work in the 'New York' location by increasing their current salary by 10%. The join with the emp_location table is used to filter only those employees who work in the 'New York' location.