How to Update From One Table to Another Based on an ID Match in SQL
Last Updated : 19 Dec, 2024
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
demo_table1
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
demo_table2
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
| ID | NAME | AGE | CITY |
| 1 | Preeti | 21 | Delhi |
| 2 | Rahul | 23 | Delhi |
| 3 | Fanny | 25 | Punjab |
| 4 | Samita | 32 | Punjab |
| 5 | Samiksha | 23 | Banglore |
| 6 | Ashtha | 24 | Banglore |
| 7 | Prem | 30 | Patna |
| 8 | Girish | 30 | Patna |
| 9 | Ram | 20 | Patna |
| 10 | Raj | 12 | Delhi |
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.
Similar Reads
SQL Server Update From One Table to Another Based on an ID Match
In the world of database management, we need to perform various OLTP operations like insert, update, and delete. The ability to efficiently update data between tables is crucial for maintaining data integrity and ensuring accurate information. SQL Server provides powerful tools to accomplish this ta
7 min read
How to Update a Table Data From Another Table in SQLite
SQLite is an embedded database that doesn't use a database like Oracle in the background to operate. The SQLite offers some features which are that it is a serverless architecture, quick, self-contained, reliable, full-featured SQL database engine. SQLite does not require any server to perform queri
3 min read
How to Copy Rows from One Table to Another in SQL?
In SQL, copying rows from one table to another is a common operation that simplifies data migration and duplication tasks. Whether creating backup tables, transferring data for analysis, or setting up a new schema, the ability to efficiently replicate data across tables is invaluable. This operation
3 min read
How to Update Records in Table from CTE in SQL
Common Table Expressions (CTEs) in SQL is an important feature that provides a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement. In this article, we will learn how to use CTEs to update records in SQL along with examples along with an explanation. Wha
4 min read
How to Copy Data From One Column to Another in the Same Table in SQL?
Efficiency in data manipulation is crucial while using Structured Query Language (SQL). To manage a wide range of tasks, including organizing, retrieving, updating, and deleting data, SQL provides a comprehensive set of instructions. Among these, copying data between columns in the same table is a c
4 min read
How to Select Rows from a Table that are Not in Another Table?
In MySQL, the ability to select rows from one table that do not exist in another is crucial for comparing and managing data across multiple tables. This article explores the methods to perform such a selection, providing insights into the main concepts, syntax, and practical examples. Understanding
3 min read
How to Select Rows with no Matching Entry in Another Table in SQLite?
In database management, selecting rows from one table that does not have matching entries in another table means returning the rows that are present in one table but do not have the same entry in any other table. This scenario often arises in various data validation and analysis processes. In this a
4 min read
How to Select All Records from One Table That Do Not Exist in Another Table in SQL?
When working with SQL databases, a common requirement is to find records from one table that do not exist in another table. This can be achieved using various SQL techniques like LEFT JOIN, NOT IN, or NOT EXISTS. In this detailed guide, we will explain how to accomplish this using SQL queries and La
4 min read
How to Update a Column in a Table in SQL Server
In the database management area, updating columns in a table is a normal query and it is important software that ensures the accuracy and integrity of data. Whether we are making spelling corrections, editing or altering existing information, or being attentive to changing requirements, carrying out
4 min read
Copy a Table from One Schema to Another in SQL?
When working with MySQL, there are several scenarios where we may need to copy tables (including their structure and data) from one schema (database) to another. This is a common task during database migrations, backups, or when testing with different environments. In this article, we will go throug
5 min read