Usage of UPDATE JOIN in MySQL

This tutorial will introduce how to use the UPDATE JOIN statement in a MySQL database.

We generally use joins to go through rows in a particular table that has or may not have similar rows in some other table. We can use the JOIN clause alongside the UPDATE statement to implement multiple table updates.

The basic syntax of MySQL UPDATE JOIN can be illustrated as follows.

UPDATE Table_1, Table_2,
[INNER JOIN] Table_1 ON Table_1.Column_1 = Table_2. Column_1
SET Table_1.Column_2 = Table_2.Column_2, 
    Table_2.Column_3 = expression
WHERE condition

The above syntax works as follows for a particular table in MySQL.

• We start our work by specifying the main table called table_1 and the table we want to join with the main table as the table_2. The table mentioned in the UPDATE clause is updated, and the data in the table not mentioned after the UPDATE clause will not be altered.
• Once we do that, we have to mention the type of join we wish to use. In the above syntax, we have used the INNER join. This join must come immediately after the UPDATE clause.
• After providing values to the columns we wish to update, we mention a WHERE clause to specify a particular condition for the update.

There is one more way to use this method that can be illustrated as follows.

UPDATE Table_1, Table_2
SET Table_1.column_2 = Table_2.column_2,
      Table_2.column_3 = expr
WHERE Table_1.column_1 = Table_2.column_1 AND condition

Before we begin, let us create two tables to work with. We will call these tables student_details and marks. These tables can be created with the following code.

CREATE TABLE marks (
    performance INT(11) NOT NULL,
    percentage FLOAT NOT NULL,
    PRIMARY KEY (performance)
);

CREATE TABLE student_details (
    stu_id INT(11) NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(255) NOT NULL,
    performance INT(11) DEFAULT NULL,
    total FLOAT DEFAULT NULL,
    PRIMARY KEY (emp_id),
    CONSTRAINT fk_performance FOREIGN KEY (performance)
        REFERENCES marks (performance)
);

INSERT INTO marks(performance,percentage)
VALUES(1,0),
      (2,0.01),
      (3,0.03),
      (4,0.05),
      (5,0.08);

INSERT INTO student_details(stu_name,performance,total)      
VALUES('Preet Sanghavi', 1, 50000),
      ('Joe Sand', 3, 65000),
      ('Su Greens', 4, 75000),
      ('Gray Dellop', 5, 125000),
      ('Neon Jonty', 3, 85000),
      ('Peter Foe', 2, 45000),
      ('Little Wayne', 3, 55000);

To visualize the student_details table, we use the following code.

SELECT * FROM student_details;

The above code would give the following output.

stu_id   stu_name 		stu_performance total
1		Preet Sanghavi		1			50000
2		Joe Sand			3			65000
3		Su Greens			4			75000
4		Gray Dellop			5			125000
5		Neon Jonty			3			85000
6		Peter Foe			2			45000
7		Little Wayne		3			55000

Similarly, we can visualize the marks table.

SELECT * FROM marks;

The above code would give the following output.

performance    percentage 
1				0
2				0.01
3				0.03
4				0.05
5				0.08

As we can see from the above code blocks, the percentage value is in the marks table, and we have to use the UPDATE JOIN with INNER JOIN as our main join to adjust the total of each student in the student_details table based on the values of percentage and performance in the marks table.

Now let us see the UPDATE JOIN statement in action.

Using the UPDATE JOIN statement.in MySQL

We can perform the update operation as described above using the following query.

UPDATE student_details
        INNER JOIN
    marks ON student_details.performance = marks.performance 
SET 
    total = total + total * percentage;

Here, the student_details table is the main table where the value of total needs to be updated.

The output of the query mentioned above would give the following results.

stu_id   stu_name 		stu_performance total
1		Preet Sanghavi		1			50000
2		Joe Sand			3			66950
3		Su Greens			4			78750
4		Gray Dellop			5			135000
5		Neon Jonty			3			87550
6		Peter Foe			2			45450
7		Little Wayne		3			56650

As we can see in the above code block, the total of each student is updated based on his/her performance in the marks table.

Thus, with the help of the above-mentioned technique, we can efficiently use Update Join in MySQL.