UPDATE query in MySQL is a DML statement used for modifying the data of a table. The UPDATE query must require the SET and WHERE clause. The SET clause is used to change the values of the column specified in the WHERE clause.
JOIN clause in MySQL is used in the statement to retrieve data by joining multiple tables within a single query.
The UPDATE JOIN is a MySQL statement used to perform cross-table updates that means we can update one table using another table with the JOIN clause condition. This query update and alter the data where more than one tables are joined based on PRIMARY Key and FOREIGN Key and a specified join condition. We can update single or multiple columns at a time using the UPDATE query.
NOTE: The MySQL UPDATE JOIN statement is supported from version 4.0 or higher.
Syntax
Following is a basic syntax of UPDATE JOIN statement to modify record into the MySQL table:
UPDATE Tab1, Tab2, [INNER JOIN | LEFT JOIN] Tab1 ON Tab1.C1 = Tab2.C1
SET Tab1.C2 = Tab2.C2, Tab2.C3 = expression
WHERE Condition; In the above MySQL UPDATE JOIN syntax:
First, we have specified the two tables: the main table (Tab1) and another table (tab2) after the UPDATE clause. After the UPDATE clause, it is required to specify at least one table. Next, we have specified the types of JOIN clauses, i.e., either INNER JOIN or LEFT JOIN, which appear right after the UPDATE clause and then a join predicate specified after the ON keyword. Then, we have to assign the new values to the columns in Tab1 and/or Tab2 for modification into the table. Finally, the WHERE clause condition is used to limit rows for updation.
How does UPDATE JOIN work in MySQL?
The UPDATE JOIN work process in MySQL is the same as described in the above syntax. But sometimes, we would find that this query alone performed the cross-table update without involving any joins. The following syntax is another way to update one table using another table:
UPDATE Tab1, Tab2,
SET Tab1.C2 = Tab2.C2, Tab2.C3 = expression
WHERE Tab1.C1 = Tab2.C1 AND condition; The above UPDATE statement produces the same result as the UPDATE JOIN with an INNER JOIN or LEFT JOIN clauses. It means we can re-write the above syntax as UPDATE JOIN syntax displayed above:
UPDATE Tab1,Tab2
INNER JOIN Tab2 ON Tab1.C1 = Tab2.C1
SET Tab1.C2 = Taba2.C2, Tab2.C3 = expression
WHERE condition Let us take some examples to understand how the UPDATE JOIN statement works in MySQL table.
UPDATE JOIN Examples
First, we will create two tables named Performance and Employee, and both tables are related through a foreign key. Here, the “Performance” is a parent table, and “Employees” is the child table. The following scripts create both tables along with their records.
Table: Performance
CREATE TABLE Performance (
performance INT(11) NOT NULL,
percentage FLOAT NOT NULL,
PRIMARY KEY (performance)
); Next, fill the records in the table using the INSERT statement.
INSERT INTO Performance (performance, percentage)
VALUES(101,0),
(102,0.01),
(103,0.03),
(104,0.05),
(105,0.08); Then, execute the SELECT query to verify the data as shown in the below image:
Table: Employees
CREATE TABLE Employees (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
performance INT(11) DEFAULT NULL,
salary FLOAT DEFAULT NULL,
CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES Performance (performance)
); Next, fill the records in the table using the INSERT statement.
INSERT INTO Employees (name, performance, salary)
VALUES('Mary', 101, 55000),
('John', 103, 65000),
('Suzi', 104, 85000),
('Gracia', 105, 110000),
('Nancy Johnson', 103, 95000),
('Joseph', 102, 45000),
('Donald', 103, 50000);Then, execute the SELECT query to verify the data as shown in the below image:
UPDATE JOIN with INNER JOIN Example
Suppose we want to update the employee’s salary on the basis of their performance. We can update an employee’s salary in the Employees table using the UPDATE INNER JOIN statement because the performance percentage is stored in the performance table.
In the above tables, we have to use the performance field to join the Employees and Performance table. See the below query:
UPDATE Employees e
INNER JOIN Performance p
ON e.performance = p.performance
SET salary = salary + salary * percentage; After executing the above statement, we will get the below output, where we can see that the employee’s salary column is updated successfully.
Let us understand how this query works in MySQL. In the query, we have specified only the Employees table after the UPDATE clause. It is because we want to change the record only in the Employees table, not in both tables.
The query checks the performance column values for each row in the “Employees” table against the performance column of the “Performance” table. If it will get the matched performance column, then it takes the percentage in the Performance table and updates the Employees table’s salary column. This query updates all records in the Employees table because we have not specified the WHERE clause in the UPDATE JOIN query.
UPDATE JOIN with LEFT JOIN Example
To understand the UPDATE JOIN with LEFT JOIN, we first need to insert two new rows into the Employees table:
INSERT INTO Employees (name, performance, salary)
VALUES('William', NULL, 73000),
('Rayan', NULL, 92000); Since these employees are new hires, so their performance record is not available. See the below output:
If we want to update the salary for newly hired employees, we cannot use the UPDATE INNER JOIN query. It is due to the unavailability of their performance data in the Performance table. Thus, we will use the UPDATE LEFT JOIN statement to fulfill this need.
The UPDATE LEFT JOIN statement in MySQL is used to update a row in a table when there are no records found in another table’s corresponding row.
For example, if we want to increase the salary for a newly hired employee by 2.5%, we can do this with the help of the following statement:
UPDATE Employees e
LEFT JOIN Performance p
ON e.performance = p.performance
SET salary = salary + salary * 0.025
WHERE p.percentage IS NULL; After executing the above query, we will get the output as below image where we can see that salary of the newly hired employees is successfully updated.
In this article, we have learned the MySQL Update Join statement that allows us to alter the existing data in one table with the new data from another table with the JOIN clause condition. This query is advantageous when we need to modify certain columns specified in the WHERE clause along with either using the INNER JOIN or LEFT JOIN clauses.
Leave a Reply