JIYIK CN >

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

We generally use joinsto iterate over the rows in a particular table which may or may not have similar rows in other tables. We can UPDATEuse JOINthe clause along with the statement to implement multiple table updates.

The basic syntax of MySQL UPDATE JOINis 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

For a specific table in MySQL, the above syntax works as follows.

• We start our work by specifying table_1the main table named and the table we want to join with the main table as . The tables mentioned in the clause are updated, and the data in the tables not mentioned after the clause is not changed.table_2UPDATEUPDATE
• Once we do that, we have to mention the type of join we wish to use. In the above syntax, we have used INNERa join. This join must come immediately UPDATEafter the with clause.
• After providing values ​​for the columns we wish to update, we mention an WHEREWITH clause to specify the specific conditions for the update.

There is another way to use this method as shown below.

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's create two tables to work with. We'll call these tables student_detailsand marks. These tables can be created using 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 student_detailsthe table, we use the following code.

SELECT * FROM student_details;

The above code will 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 marksthe table.

SELECT * FROM marks;

The above code will give the following output.

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

From the above code block we can see that the percentage values ​​are marksin the table and we have to use UPDATE JOINand INNER JOINas our primary join to adjust each student's total student_detailstable based on the values ​​of and marksin the table .percentageperformance

Now let's see UPDATE JOINwhat the statement actually does.

UPDATE JOINUsing statement.in in MySQL

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

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

Here, student_detailstable is totalthe main table whose values ​​need to be updated.

The output of the above query will give the following result.

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, each student's is updated 总分according to his/her status in 分数the table .表现

So, with the help of above mentioned techniques, we can use in MySQL effectively Update Join.