Updating Data with Joins in SQL Queries

1. Introduction

In SQL, the UPDATE statement is a powerful clause for modifying existing records in a database. Often, we need to update data in one table based on information from another related table. This is when we apply UPDATE JOIN. In this tutorial, we’ll explore using UPDATE statements with JOINs to achieve this.

In this tutorial, we’ll learn the syntax for UPDATE JOIN and how to use it with WHERE and CASE. We’ll also look at different ways to update a table using UPDATE JOIN in SQL. This tutorial uses the “Exam and Student” table from the full schema.

2. The UPDATE JOIN Concept

UPDATE JOIN is used to update records with matching columns in another table where a certain condition needs to be met for the values to be changed. This is useful in scenarios where we need to update a table record based on related information in other tables.

The general syntax for this is:

UPDATE table1
SET table1.column1 = table2.column2,
    table1.column2 = expression
FROM table1
JOIN table2 
    ON table1.key = table2.key
WHERE condition;

The above query uses the UPDATE JOIN syntax to join data under multiple conditions. However, UPDATE JOIN can also be applied to more than one JOIN, as long as all the tables have a relationship that can be connected with the JOIN clause.

3. Implementing UPDATE JOIN

There are several approach to implementing UPDATE JOIN, which depends on the data schema. We can implement UPDATE JOIN with WHERE clause or with the CASE conditional statement.

3.1. UPDATE JOIN with WHERE

We choose to use the Student and Exam table due to the relationship between them. Before implementing UPDATE JOIN, let’s take a look at the current state of the tables.

First, let’s display the data we have in the Student table:

SELECT * FROM Student 
LIMIT 10;
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| id   | name            | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| 1001 | John Liu        |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora        |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose     |   321345566 | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
| 1010 | Samantha Prabhu |  3217165566 | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9 |
| 1011 | Vikas Jain      |   321345662 | 2001-07-18 | 2020-01-15      | NULL            |  3.3 |
| 1101 | Jia Grey        |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98 |
| 1103 | Rose Rit        |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            | 3.57 |
| 1107 | Phellum Luis    |   203678911 | 2001-03-15 | 2020-01-15      | 2024-06-15      | 4.21 |
| 1110 | Albert Decosta  |  2617897011 | 2001-02-21 | 2020-01-15      | 2024-06-15      |    4 |
| 1111 | Vikram Kohli    |  1516578091 | 2001-03-08 | 2020-01-15      | 2024-06-15      | 3.27 |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
10 rows in set (0.001 sec)

The above output is the student’s data from the Student table. We added LIMIT to the query to display only the first 10 records.

Next, let’s take a look at a JOIN of the Exam and Student table. In this case we JOIN the both tables ON their respective keys and LIMIT the output to 10 rows:

SELECT * FROM Exam 
JOIN Student
ON  Exam.student_id = Student.id
LIMIT 10;
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
| id | semester | exam_date  | grade | course_id | student_id | id   | name     | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
|  1 | SPRING   | 2022-07-11 | A+    | CS111     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  2 | SPRING   | 2022-07-11 | A     | CS121     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  3 | SPRING   | 2022-07-11 | A+    | CS122     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  4 | FALL     | 2022-12-05 | B+    | CS211     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  5 | FALL     | 2022-12-06 | B     | CS212     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  6 | FALL     | 2022-12-07 | B+    | MA111     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  7 | SPRING   | 2023-07-11 | A+    | CS411     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  8 | SPRING   | 2023-07-11 | A+    | CS511     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  9 | SPRING   | 2023-07-11 | F     | MA211     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 10 | FALL     | 2023-12-05 | NULL  | CS121     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
10 rows in set (0.000 sec)

The above table shows the first 10 rows of the Exam and Student table using JOIN.

Suppose we want to update the student’s grade in the Exam table based on their national_id in the Student table. Before we apply UPDATE JOIN, the aim is to change the grade of student that have NULL grade whose national_id is ‘123345566’:

Let’s now apply UPDATE JOIN using the WHERE clause:

UPDATE Exam
SET e.grade = 'C'
FROM Exam AS e
JOIN Student AS s
    ON e.student_id = s.id
WHERE s.national_id = 123345566
    AND e.grade IS NULL;

To better understand how the query works, each query line is further explained in their written order:

• UPDATE Exam: this statement specifies that we want to update the Exam table
• JOIN Student: with JOIN, we specify to the database engine to merge the Student table with the Exam table, which we want to UPDATE
• ON e.Student_id = s.id: here we use the ON keyword to define the key or condition for joining the two tables. This key is usually relative to both tables.
• SET e.grade = ‘C’: we use SET to declare the column which we want to update and the value. Here, we change the grade of the grade column to “C”
• WHERE s.national_id = 123345566 AND e.grade IS NULL: the WHERE clause filters the column and ensures that only students with the national_id and student_id are updated

The query updates the grade of the student whose national_id and grade correspond with the values in the WHERE clause.

Let’s confirm the new update we made to the Exam table:

SELECT * FROM Exam AS e 
JOIN Student AS s 
ON e.student_id = s.id 
WHERE national_id = 123345566;
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
| id | semester | exam_date  | grade | course_id | student_id | id   | name     | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
|  1 | SPRING   | 2022-07-11 | A+    | CS111     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  2 | SPRING   | 2022-07-11 | A     | CS121     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  3 | SPRING   | 2022-07-11 | A+    | CS122     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  4 | FALL     | 2022-12-05 | B+    | CS211     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  5 | FALL     | 2022-12-06 | B     | CS212     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  6 | FALL     | 2022-12-07 | B+    | MA111     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  7 | SPRING   | 2023-07-11 | A+    | CS411     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  8 | SPRING   | 2023-07-11 | A+    | CS511     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
|  9 | SPRING   | 2023-07-11 | F     | MA211     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 10 | FALL     | 2023-12-05 | C     | CS121     |       1001 | 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
+----+----------+------------+-------+-----------+------------+------+----------+-------------+------------+-----------------+-----------------+------+
10 rows in set (0.001 sec)

3.2. UPDATE JOIN With CASE

To illustrate, suppose we want to modify the graduation_date in the Student table for students whose program starts in the “SPRING” semester. We can use UPDATE JOIN with CASE to change the graduation_date, since the “SPRING” semester begins in January and ends in June and the “FALL” semester begins in August and ends in December.

For this section, let’s write an UPDATE JOIN along with the CASE condition to change the gradution_date for student whose semester started in the “FALL” to ‘2024-12-15’ and “SPRING” to ‘2024-06-15’ excluding rows with NULL values:

UPDATE Student
SET graduation_date = CASE
    WHEN e.semester = 'FALL' THEN '2024-12-15'
    WHEN e.semester = 'SPRING' THEN '2024-06-15'
    ELSE NULL
    END
FROM Student AS s
JOIN Exam AS e
ON s.id = e.student_id;

The query above updates the Student table based on the condition in each WHEN clause and the record is SET to NULL if the records do not belong to any of “FALL” or “SPRING”, it is replaced with NULL value.

To see the output, use the next query and LIMIT the result to 10 rows:

SELECT * FROM Student AS s 
JOIN Exam AS e 
ON e.student_id = s.id 
WHERE e.semester = 'SPRING' 
LIMIT 10;
+------+----------+-------------+------------+-----------------+-----------------+------+----+----------+------------+-------+-----------+------------+
| id   | name     | national_id | birth_date | enrollment_date | graduation_date | gpa  | id | semester | exam_date  | grade | course_id | student_id |
+------+----------+-------------+------------+-----------------+-----------------+------+----+----------+------------+-------+-----------+------------+
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  1 | SPRING   | 2022-07-11 | A+    | CS111     |       1001 |
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  2 | SPRING   | 2022-07-11 | A     | CS121     |       1001 |
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  3 | SPRING   | 2022-07-11 | A+    | CS122     |       1001 |
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  7 | SPRING   | 2023-07-11 | A+    | CS411     |       1001 |
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  8 | SPRING   | 2023-07-11 | A+    | CS511     |       1001 |
| 1001 | John Liu |   123345566 | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |  9 | SPRING   | 2023-07-11 | F     | MA211     |       1001 |
| 1003 | Rita Ora |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 | 21 | SPRING   | 2022-07-11 | A     | CS121     |       1003 |
| 1003 | Rita Ora |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 | 22 | SPRING   | 2022-07-11 | A+    | CS122     |       1003 |
| 1003 | Rita Ora |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 | 23 | SPRING   | 2022-01-11 | A+    | CS123     |       1003 |
| 1003 | Rita Ora |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 | 27 | SPRING   | 2023-07-19 | B     | CS421     |       1003 |
+------+----------+-------------+------------+-----------------+-----------------+------+----+----------+------------+-------+-----------+------------+
10 rows in set (0.129 sec) 

We’ve now updated the graduation_date for students whose semester started during the ‘SPRING’. Moreover, UPDATE JOIN with CASE is applicable when changing values based on multiple conditions, allowing us to write short, efficient, and well-optimized JOIN queries.

4. Common Errors When Using UPDATE JOINS

Applying UPDATE JOIN requirements may lead to incorrect results in some cases. Therefore, it is important to understand the common drawbacks users face when applying this concept.

Common drawbacks include:

• Incorrect JOIN condition: incorrect JOIN conditions sometimes lead to an unwanted table update. To prevent this, we need to double-check the clauses to identify mistakes in the JOINS before updating the table
• Unintended full table update: UPDATE JOIN sometimes results in a full table update. This may happen if the WHERE clause is missing in the query. To avoid this scenario, we need to ensure that the WHERE clause is specified and the conditions are specific to the target records
• Omitted changes: if the table specified in the UPDATE clause is not in the SET clause, some changes are omitted. To handle this, we should ensure that the table in the UPDATE and FROM clauses are the same

5. Conclusion

In this article, we explored numerous approaches to using UPDATE JOIN. We also learned about the common errors when working with them and how to handle these errors.

In conclusion, UPDATE JOIN is a flexible approach that works with multiple tables in SQL. It changes the value only in the parent column without creating a new table. Furthermore, implementing UPDATE JOIN with a CASE clause requires us to give specific conditions for the values we want to change. It is more applicable when we have numerous conditions and need to handle NULL values.