MySQL UPDATE JOIN

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. The "update join" query modifies and operates join columns. This query can work on single or multiple tables and always requires the SET and WHERE clauses. The SET operator uses the regular column or row to modify the table value. The "WHERE" clause applies the join condition on the table. Sometimes, the update join works on several tables using primary key and foreign key.

Syntax

The syntax of the update inner join shows below.

UPDATE table1, table2 INNER JOIN table1 
ON table1.column1 = table2.column1
SET table1.column2 = table2.column2, table2.column2 = expression
WHERE join_condition;

The syntax of the update left join shows below.

UPDATE table1, table2 LEFT JOIN [RIGHT JOIN] table1 
ON table1.column1 = table2.column1
SET table1.column2 = table2.column2, table2.column2 = expression
WHERE join_condition;

Basic settings of the update join

• Create the first table department with a category of education columns.

mysql> CREATE TABLE department (
    	department_id INT AUTO_INCREMENT,
    	department_name VARCHAR(45),
    	admissions INT,
    	PRIMARY KEY (department_id));

• Insert data into the department table. Here, the table includes the department name and number of the admissions.

mysql> INSERT INTO department (department_name, admissions) VALUES("Bachelor of Arts", 45), ("Bachelor of Commerce", 55), ("Bachelor of Engineering", 50), ("Bachelor of Technology", 60);

• Create a second table subject with the required columns.

mysql> CREATE TABLE subject (
	subject_id INT AUTO_INCREMENT,
    	subject_name VARCHAR(45),
    	students INT,
   	PRIMARY KEY (subject_id));

• Insert data into the subject table. Here, the table includes the subject name and number of the students.

mysql> INSERT INTO subject (subject_name, students) VALUES("Electronics", 20), ("Electrical", 25), ("Computer Science", 15), ("Information Technology", 30);

• You can see the output of the department table structure and its data using the below query.

mysql> SELECT * FROM department;

The above image shows the information of the table, such as department id, department name, and the number of the admission.

• You can see the output of the subject table structure and its data using the below query.

mysql> SELECT * FROM subject; 

• Add the foreign key to the department table. The subject_id column is a common column that links department and subject table.

Examples of the MySQL update join

1) Example: update the inner join with the "WHERE" clause example shows below.

Execute the below query to set the admissions column whose value increases by 1. You need to use the "ON" and SET keywords to change specific columns. The "UPDATE" keyword requires for modifying the given table.

mysql> UPDATE department d INNER JOIN subject s ON d.subject_id = s.subject_id SET admissions = admissions + 1 WHERE department_id < 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows that the admission number increases. This column changes from 44 to 45 numbers. Here, the table change data of the first row of the column.

2) Example

The following query will set the admissions column in the table whose value decreases by 1. The "ON" and SET keywords need to change specific columns. The "UPDATE" keyword requires for modifying the given table. The "WHERE" clause updates the specific row.

mysql> UPDATE department d INNER JOIN subject s ON d.subject_id = s.subject_id SET admissions = admissions - 2 WHERE department_id < 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows that the admission number is decremented from 45 to 43 numbers. Here, the table changes the data of the first row of the column.

3) Example: update the inner join with the "SET" operator example shows below.

The following query will set the admissions column in the table whose value decreases by 10. The "ON" and SET keywords are used to change specific columns. The UPDATE keyword requires modifying the given table.

mysql> UPDATE department d INNER JOIN subject s ON d.subject_id = s.subject_id SET admissions = admissions – 10;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows that the admission number is decremented from 43 to 33 numbers. Here, the table data changes the entire rows of the column.

4) Example: update the right join with the "SET" operator example shows below.

The following query will set the admissions column in the table whose value increases by 10. You need to use the "ON" and SET keywords to change specific columns. The UPDATE keyword requires for modifying the given table.

mysql> UPDATE department d RIGHT JOIN subject s ON d.subject_id = s.subject_id SET admissions = admissions * 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows the change in the admission number. This column changes from the original number to double numbers of the origin. Here, the table data changes the entire rows of the column.

5) Example: update the multiple columns using the left join example shows below.

Execute the following query to update the table using left join. The multiple columns changes by using the clause and operator.

mysql> UPDATE department d LEFT JOIN subject s ON d.subject_id = s.subject_id SET department_name = " Bachelor of Agriculture", admissions = admissions / 2 WHERE department_id = 2;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows the change in the department name and admission number. This column changes string and number. Here, the table data changes the second row of the column.

6) Example: update the right join with the "WHERE" clause example shows below.

Execute the following query to update the table using the right join. The department name changes by using the clause and operator.

mysql> UPDATE department d RIGHT JOIN subject s ON d.subject_id = s.subject_id SET department_name = " Bachelor of Fine Arts" WHERE department_id = 1;

OUTPUT

Execute the below query to get updated table data.

mysql> Select * from department;

The above image shows the change of the department name. This column changes from the "bachelor of arts" number to the "bachelor of fine arts". Here, the table data changes the first row of the column.