How to Update Table From Another Table in MySQL

In this tutorial, we aim at exploring how to update values of a table based on entries of another table in MySQL.

Many-a-times, it becomes necessary for businesses and organizations to update values of a particular table constantly. Moreover, these values may need to be updated based on the entries in another table. This another table might be linked to the table to be updated based on one or more columns.

For example, consider we have two tables. One is student_details and other is student_ids. Both of these tables have a common column named stu_firstName. We wish to update student_ids table with the identity number of the students using matching information from the student_details table. This can be done with the help of an UPDATE JOIN clause.

Let us understand how this method works.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. In order to view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Let us try to create another table named student_ids with the columns stu_id and stu_firstName that indicate the identity number and names of the students. We can do this with the following query.

CREATE TABLE student_id(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL
);

INSERT INTO student_id(stu_id, stu_firstName) 
 VALUES(1,"Preet"),
 (1,"Rich"),
 (1,"Veron"),
 (5,"Geo"),
 (5,"Hash"),
 (5,"Sachin"),
 (5,"David");

We can visualize this table as follows.

select * from student_id;

The output of the aforementioned code can be illustrated as follows.

stu_id	stu_firstName
1		Preet
1		Rich
1		Veron
5		Geo
5		Hash
5		Sachin
5		David

Now, let us try to update the student_id table using the student_details table.

UPDATE JOIN in MySQL

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

UPDATE table_2
INNER JOIN table_1 ON table_2.name = table_1.name
SET table_2.value = IF(table_1.value > 0, table_1.value, table_1.value)

As we can see, in the aforementioned query, we update the value of value in table_2 based on the value of value in table_1. We can do this with the following query.

UPDATE student_id
INNER JOIN student_details ON student_id.stu_firstName = student_details.stu_firstName
SET student_id.stu_id = student_details.stu_id;

The aforementioned code updates the values of the stu_id column of the student_id table. The output of the aforementioned code can be illustrated as follows.

stu_id	stu_firstName
1		Preet
2		Rich
3		Veron
4		Geo
5		Hash
6		Sachin
7		David

As we can see, the column name stu_id is updated based on the values of the table student_details. This join is performed using the common table name stu_firstName. This stu_firstName values are matched for both the columns and the corresponding value of the stu_id column from the student_details table are reflected in the student_id table.

An alternative to this solution would be to avoid the usage of INNER JOIN and directly perform a join with the help of the WHERE ON clause. The aforementioned operation can be done with this technique as follows.

UPDATE student_id
SET student_id.stu_id = (
	SELECT student_details.stu_id 
    from student_details
    WHERE student_details.stu_firstName = student_id.stu_firstName
);

Here, as we can see, The output of the aforementioned code can again be illustrated with the command SELECT * from student_id to get results as follows.

stu_id	stu_firstName
1		Preet
2		Rich
3		Veron
4		Geo
5		Hash
6		Sachin
7		David

Therefore, with the help of the UPDATE JOIN technique, we can efficiently update a column of one table based on values from another table in MySQL.