Update Data From One SQL Table to Another Based on ID Match

1. Overview

While working in database management, we often need to update data in one SQL table based on information from another table. Moreover, we do this to maintain data consistency and ensure that our database stays accurate and updated. We can also update data from one table to another based on ID matching using UPDATE with INNER JOIN, a WHERE clause, subqueries, and other methods.

Moreover, ID matching typically refers to the process of finding records in one table that have corresponding records in another table based on a common identifier.

In this tutorial, we’ll discuss various ways to update data from one SQL table to another based on ID matching.

2. Creating a Sample Table

We’ll perform all our methods in different database management systems such as SQL Server, MySQL, and PostgreSQL. Additionally, we’ll use sample tables from our Baeldung University database schema.

Furthermore, we’ll update the Department table columns of the Baeldung University database schema with another sample table.

Firstly, let’s view the content of the Department table of our Database:

SELECT * FROM department;
+----+--------------------------------+------+ 
| id | name                           | code | 
|----+--------------------------------+------| 
| 1  | Computer Science               | CS   | 
| 2  | Electronics and Communications | EC   | 
| 3  | Mechanical Engineering         | ME   | 
| 4  | Civil Engineering              | CE   | 
| 5  | Mathematics                    | MA   | 
+----+--------------------------------+------+

Here, we need to update the name and code columns of the Department table with the values from our new sample table.

Before updating, let’s create a new sample table using this query:

CREATE TABLE New_Department
(
id INT PRIMARY KEY NOT Null,
name VARCHAR (50),
code VARCHAR (4),
UNIQUE (id)
);

Furthermore, let’s populate this table with sample data:

INSERT INTO New_Department (id, name, code) VALUES
(1, 'Telecom Engineering', 'TE'),
(2, 'Electronics and Communications', 'EC'),
(3, 'Mechatronics Engineering', 'MET'),
(4, 'Civil Engineering', 'CE'),
(5, 'Software Engineering', 'SE');

Additionally, we can view it using the SELECT and FROM statements:

SELECT * FROM new_department;
+----+--------------------------------+------+ 
| id | name                           | code | 
|----+--------------------------------+------| 
| 1  | Telecom Engineering            | TE   | 
| 2  | Electronics and Communications | EC   | 
| 3  | Mechatronics Engineering       | MET  | 
| 4  | Civil Engineering              | CE   | 
| 5  | Software Engineering           | SE   | 
+----+--------------------------------+------+

Moreover, the New_Department table contains the id, name, and code columns with different name and code values.

3. Using UPDATE

We can use the UPDATE statement in conjunction with an INNER JOIN or WHERE clause to update data from one table to another based on ID matching. Additionally, we can use the SET command to specify the columns and new values we want to update.

Furthermore, we can perform INNER JOIN conditions in SQL Server and MySQL with slightly different syntax. However, we can’t directly perform an INNER JOIN operation in PostgreSQL due to aliasing issues. So, we need to use the implicit approach with the WHERE clause instead of INNER JOIN in PostgreSQL.

We can also use WHERE clauses in MySQL and SQL Server, as this approach is universal.

3.1. SQL Server

Let’s consider an example of an SQL query that can perform an INNER JOIN between Department and New_Department on the ID column:

UPDATE department
SET department.name = new_department.name,
department.code = new_department.code
FROM department
INNER JOIN new_department
ON department.id = new_department.id;

In this query, INNER JOIN returns rows when there’s at least one match in both tables based on the condition. Moreover, if any records in the department don’t have a corresponding record in New_Department (based on id), those records won’t be updated.

Furthermore, let’s view the updated table with this query:

SELECT * FROM department;
+----+--------------------------------+------+ 
| id | name                           | code | 
|----+--------------------------------+------| 
| 1  | Telecom Engineering            | TE   | 
| 2  | Electronics and Communications | EC   | 
| 3  | Mechatronics Engineering       | MET  | 
| 4  | Civil Engineering              | CE   | 
| 5  | Software Engineering           | SE   | 
+----+--------------------------------+------+

Moreover, the Department table of the University database is updated with a new value based on ID matching at positions 1, 3, and 5.

We can also simplify our previous query by using the WHERE clause instead of the INNER JOIN. For example, we can use a comma-separated list of tables with a WHERE clause to specify the join condition:

UPDATE department
SET department.name = new_department.name,
department.code = new_department.code
FROM department, new_department
WHERE department.id = new_department.id

This query also produces the same result as the previous query.

3.2. MySQL

Firstly, we need to perform the INNER JOIN operation to combine the rows specified in the JOIN clause. After performing the JOIN operation, we can apply the SET clause to update the specified columns in those rows.

Furthermore, let’s update the Department table in MySQL:

UPDATE department
INNER JOIN new_department
ON department.id = new_department.id
SET department.name = new_department.name,
department.code = new_department.code

Furthermore, when we try to update the table without a WHERE clause in MySQL, we need to disable the safe mode option. Additionally, in MySQL Workbench, this option can be accessed from the Edit>Preferences>SQL Editor settings.

We can also update the tables in MySQL with the WHERE clause:

UPDATE
department,
new_department
SET
department.name = new_department.name,
department.code = new_department.code
WHERE
department.id = new_department.id;

Moreover, this query is equivalent to the previous query and will produce the same output.

3.3. PostgreSQL

In PostgreSQL, the UPDATE statement with a FROM clause acts as an implicit JOIN. Furthermore, we can’t directly use INNER JOIN without using the WHERE clause. However, both perform similar functionality and produce the same output.

Let’s update the Department table:

UPDATE department dept
SET name = new_dept.name,
code = new_dept.code
FROM new_department new_dept
WHERE new_dept.id = dept.id;

The Department table is updated based on ID match with another table.

4. Using UPDATE With Subquery

We can also use a subquery within the UPDATE statement to update the existing records of a specified table. Additionally, the subquery method is universal and works on almost all the dialects such as SQL Server, MySQL, and PostgreSQL.

Moreover, we can update the Department table using the UPDATE statement with a subquery:

UPDATE department
SET name = (SELECT name FROM new_department WHERE new_department.id = department.id),
code = (SELECT code FROM new_department WHERE new_department.id = department.id)
WHERE department.id IN (SELECT id FROM new_department)

Furthermore, we can run SELECT * FROM department in the terminal to view the updated table.

5. Using MERGE INTO

The MERGE INTO statement, also known as UPSERT (update or insert), allows us to update existing records or insert new records based on a condition. Additionally, we can use the MERGE INTO statement with other clauses such as USING, WHEN, THEN, and SET to update data from one SQL table to another based on ID matching.

Furthermore, this method will work on SQL Server and PostgreSQL, while MySQL doesn’t support the MERGE INTO statement.

5.1. SQL Server

We can update the specified table using MERGE INTO:

MERGE INTO department
USING new_department
ON department.id = new_department.id
WHEN MATCHED THEN UPDATE
SET department.name = new_department.name,
department.code = new_department.code;

Similarly, we can view our updated table with the SELECT and FROM statements.

5.2. PostgreSQL

In PostgreSQL, the MERGE INTO statement uses a slightly different syntax, especially in defining the aliases. For example, we need to define aliases for Department and New_Department tables using the AS keyword.

Furthermore, let’s update the department table using this query:

MERGE INTO department AS dept
USING new_department AS new_dept
ON dept.id = new_dept.id
WHEN MATCHED THEN UPDATE
SET name = new_dept.name,
code = new_dept.code
WHEN NOT MATCHED THEN
INSERT (id, name, code)
VALUES (new_dept.id, new_dept.name, new_dept.code);

Moreover, the above query returns the same output as the previous section.

6. Conclusion

In this article, we’ve explored different ways to update data from one table to another based on ID matching.

Firstly, we explored the UPDATE with the INNER JOIN clause method, along with its alternative, the WHERE clause method. Subsequently, we demonstrated the universal subquery method that’s used across all dialects.

Lastly, we explored the MERGE INTO method that’s available for SQL Server and PostgreSQL with different syntaxes.