How to Update From One Table to Another Based on an ID Match in SQL

In SQL, updating data between tables is a common operation used to maintain data consistency and accuracy across related datasets. Whether we need to synchronize records, update fields, or correct discrepancies, SQL provides efficient methods to achieve this.

In this article, we will explain how to use the UPDATE statement to update data in one table from another based on ID match. The update statement is always followed by the SET command. The SET command is used to specify which columns and values need to be updated in a table.

Understanding the UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. When combined with a SET command, it specifies which columns and values need to be updated. To update a table based on another table’s values, the statement includes a JOIN clause to match corresponding rows. For updates based on another table, the query typically includes a JOIN or a FROM clause, as shown later in the example.

Syntax:

UPDATE table_name
SET column_name = value
WHERE condition;

Example of Update From One Table to Another Based on an ID Match

To demonstrate the update process, we will use two tables: demo_table1 (the target table) and demo_table2 (the source table) from the geek's database. The goal is to update records in demo_table1 using matching data from demo_table2 based on the ID column.

1. Creating the Target Table: demo_table1

The demo_table1 table is the target table where updates will be applied. It contains the following columns: ID, NAME, AGE, and CITY. This table will have its NAME and AGE columns updated using matching records from the source table, demo_table2, based on the ID column.

Query:

CREATE TABLE demo_table1(
ID int,
NAME VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );

INSERT INTO demo_table1 VALUES
(1,'Romy',23,'Delhi'),
(2,'Rahul',23,'Delhi'),
(3,'Nikhil',24,'Punjab'),
(4,'Ranvir',23,'Punjab'),
(5,'Samiksha',23,'Banglore'),
(6,'Ashtha',24,'Banglore'),
(7,'Tannu',30,'Patna'),
(8,'Girish',30,'Patna'),
(9,'Ram', 20 , 'Patna'),
(10,'Raj', 12, 'Delhi');

SELECT * FROM demo_table1;

Output

2. Creating the Source Table: demo_table2

The demo_table2 table serves as the source table containing updated data for specific records. It includes the ID, NAME, and AGE columns, and its data will be used to update matching records in the target table, demo_table1, based on the ID column.

Query:

CREATE TABLE demo_table2(
ID int,
NAME VARCHAR(20),
AGE int);

INSERT INTO demo_table2 VALUES
(3,'Fanny',25 ),
(7,'Prem', 30),
(1,'Preeti',21),
(4,'Samita',32);SELECT * FROM demo_table2;

Output

Updating Data Between Tables

For the demonstration, update all the fields of demo_table1 from demo_table2 based on ID match. If two table has the same column name we can write column name using syntax "table_name.column_name" to avoid confusion. By clearly specifying table and column names, we reduce confusion and avoid errors during query execution.

For example:

• demo_table1.ID: Refers to the ID column in demo_table1
• demo_table2.ID: Refers to the ID column in demo_table2
• Both tables also have a NAME column, which is clearly identified by prefixing it with the table name.

Query:

UPDATE demo_table1
SET demo_table1.NAME=demo_table2.NAME, 
demo_table1.AGE=demo_table2.AGE
FROM demo_table1, demo_table2
WHERE demo_table1.ID=demo_table2.ID;

Output

Explanation:

After executing the update query, the demo_table1 table reflects the changes for rows where matching ID values exist in both tables. Specifically, the NAME and AGE columns in demo_table1 are updated with the corresponding values from demo_table2. This ensures that only the intended records are updated, preserving the integrity of the remaining data.

Key Tips and Best Practices

• Use Table Aliases: When updating multiple tables, aliases help improve query readability and avoid column name conflicts.
  
• Check for Matching Rows: Use INNER JOIN or EXISTS to ensure only relevant rows are updated.
  
• Backup Data: Always back up your data before running update queries, especially in production environments

Conclusion

Updating one table based on another table is essential for maintaining accurate and consistent data in relational databases. By Using JOINs and the UPDATE statement, we can efficiently update specific records while preserving data integrity. This guide provided detailed examples, syntax, and best practices to help us perform such operations effectively. Implement these techniques to simplify our database management tasks and ensure seamless synchronization across tables.

romy421kumari

Follow

Improve

Article Tags :

• SQL
• Databases
• SQL-Server
• SQL-Query