MySQL Update Join

Introduction to MySQL Update Join

MySQL Update Join is a MySQL statement that performs a cross-table update using the JOIN MySQL clauses in the UPDATE MySQL query command. The MySQL Update Join is used for executing the update statement together with the implementation of INNER JOIN and LEFT JOIN MySQL clauses in the server. This Update JOIN clause in MySQL helps to retrieve the data records from the related database tables along with modifying them with the query. This query process causes a cross-table update where one database tables are joined and altered using others based on PRIMARY and FOREIGN Keys defined on the tables and a join condition in the database server.

Syntax

The MySQL Update Join statement has the following basic syntax code to perform the update query in the database:

UPDATE Table1, Table2, [LEFT JOIN clause| INNER JOIN clause] Table1 ON Table1.ColA = Table2.ColA
SET Table1.ColB = Table2.ColB, Table2.ColC = Expression
WHERE Condition

Explanation: Let us elaborate on the terms included in the above syntax structure:

Firstly, take the main table (Table1) and another table (Table2) which is used to join the main table implemented after the UPDATE query. After the UPDATE clause, it is necessary to identify at least one table otherwise if the table is not specified then the data rows in the table will not be updated on the execution of the UPDATE query.

Secondly, we need to state the JOIN clause right after the clause UPDATE. You can take any type of JOIN you want to apply from the two mentioned above i.e. INNER JOIN or LEFT JOIN and together with a predicate of join.

Now, you will set the new values and assign them to both table columns i.e. Table1 and Table2, that you want to modify. The expression denotes any expressional values or column fields to be mentioned in the update clause. Finally, you can put a Join condition using the WHERE clause to provide a limit records to records for updating.

How does MySQL Update Join work?

The work process is the same as explained in the syntax described above. But in some cases, you may find the UPDATE query to alone perform the cross-table update without involving Joins. This is another way whose syntax can be written as:

Code:

UPDATE Table1, Table2, SET Table1.ColB = Table2.ColB, Table2.ColC = Expression WHERE Table1.ColA = Table2.ColBAND Condition

The above UPDATE command works similarly to UPDATE JOIN together with an implicit INNER JOIN or LEFT JOIN MySQL clauses. Thus, the above syntax can be redesigned as UPDATE JOIN syntax displayed above and query can be executed and the desired result can be gained.

We use MySQL Update Join to update the column values of one main table with the new values of other tables related to the previous table using either INNER JOIN or LEFT JOIN on both table column values. Also, the query is also filtered by the WHERE clause against certain specified conditions so that the rows of the table can be updated properly.

You can also apply NULL values to any column values by specifying in the UPDATE statement. We can also set multiple column values to update multiple table columns which can be parted by comma (,).

Examples to Implement MySQL Update Join

For having better knowledge to understand the UPDATE JOIN in MySQL let us consider and use the below examples and notify the working of Update statement with Joins and WHERE clauses.

For demonstration, let us create a database for a sample to use in the query which includes the tables to be applied for the MySQL Update Join. Suppose, we have created two tables in the database:

Create a table named Students which contains student’s data like student ids, performances, salaries, and Student names. The other table Merit is created having columns for student performances and percentage of merit.

Let’s start to create a database first and if already present then load it using the following statement:

Code:

CREATE DATABASE IF NOT EXISTS StudDb;

Now, under this database, we will design two tables as mentioned above Students and Merit which will be used for further Update Join query.

SQL statement to create a Merit table:

Code:

CREATE TABLE Merit ( Mperformance INT NOT NULL, Mpercentage FLOAT NOT NULL, PRIMARY KEY (Mperformance) );

 SQL statement to create Students table:

Code:

CREATE TABLE Students ( StudID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, StudName VARCHAR(255) NOT NULL, Mperformance INT DEFAULT NULL, salary FLOAT DEFAULT NULL, CONSTRAINT fk_mperformance FOREIGN KEY (Mperformance) REFERENCES Merit (Mperformance) );

Also, inserting some data values to both the tables using the SQL command below:

SQL statement to insert rows in Students table:

Code:

INSERT INTO Students(StudName,Mperformance,Salary) VALUES('Nikhil Sharma', 10, 8000), ('Rita Jain', 12, 9000), ('Sangam Kumar', 11, 4500), ('Divya Verma', 14, 6500), ('Anita Shah', 13, 8500);

SQL statement to insert rows in Merit table:

Code:

INSERT INTO Merit(Mperformance,Mpercentage) VALUES(10,0.02),(11,0.04),(12,0.25),(13,0.21),(14,0.05);

Again, you can view the data records using the query:

SQL statement:

Code:

SELECT * FROM Students;

Output:

SQL statement:

Code:

SELECT * FROM Merit;

Output:

Example #1:

Using MySQL UPDATE JOIN with INNER JOIN clause

Using INNER join let us update the Salary column values in Students table based on the student performance and percentage calculation from Merit table using the succeeding query:

Code:

UPDATE students INNER JOIN Merit ON students.Mperformance = Merit.Mperformance SET Salary = Salary + Salary * Mpercentage;

Output:

Explanation: Since the performance column is linking the two tables so, the Salary values are adjusted according to the percentage values respectively to new values as shown in the above output.

Example #2:

Using MySQL UPDATE JOIN with LEFT JOIN clause& WHERE clause

Suppose, we have included two more students that were newly submitted in the student’s table, so their performance data are not available and values are set to NULL.

Code:

select * from `students`

Output:

Due to this, we cannot use the INNER JOIN to update the table as no increment base is present in the other table Merit. For this, the LEFT JOIN will be implemented to update for the newly admitted ones with NULL performance values. We can set some percentage value to calculate the salary for new students like 1.25% using the UPDATE LEFT JOIN statement as follows:

Code:

UPDATE students LEFT JOIN Merit ON students.Mperformance = Merit.Mperformance
SET Salary = Salary + Salary * 0.0125 WHERE Merit.Mpercentage IS NULL;

Output:

select * from `students`

Conclusion

The MySQL Update Join is a MySQL query to update the existing record in one table with the new record values from the other database table together with the JOIN clause condition. This query is important to make some modifications in certain columns specified through the WHERE clause in the statements containing the INNER JOIN and LEFT JOIN clauses.

Recommended Articles

This is a guide to MySQL Update Join. Here we discuss an introduction to MySQL Update Join, syntax, how does it work with respective query examples. You can also go through our other related articles to learn more –

1. MySQL Index
2. MySQL Subquery
3. NOT in MySQL
4. Cursor in MySQL