SQL Query to Update From One Table to Another Based on an ID Match
In this article, we will see, how to update from one table to another table based on ID match. We can update the table using UPDATE statement in SQL. 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.
UPDATE syntax:
UPDATE table_name SET column_name = value WHERE condition;
To perform the above function, we can set the column name to be equal to the data present in the other table, and in the condition of the WHERE clause, we can match the ID.
Now, for the demonstration follow the below steps:
Step 1: Create a database
we can use the following command to create a database called Beginner.
Query:
CREATE DATABASE Beginner;
Step 2: Use database
Use the below SQL statement to switch the database context to Beginner:
Query:
USE Beginner;
Step 3: Table definition
We have two tables named ‘demo_table1’ and ‘demo_table2’ in our geek’s database.
Query(demo_table1):
CREATE TABLE demo_table1( ID int, NAME VARCHAR(20), AGE INT, CITY VARCHAR(20) );
Query(demo_table2):
CREATE TABLE demo_table2( ID int, NAME VARCHAR(20), AGE int);
Step 4: Insert data into a table
Query(demo_table1):
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');
Query(demo_table2):
INSERT INTO demo_table2 VALUES (3,'Fanny',25 ), (7,'Prem', 30), (1,'Preeti',21), (4,'Samita',32);
Step 5: View the content
Execute the below query to see the content of the table
Query(demo_table1):
SELECT * FROM demo_table1;
Output:
Query(demo_table2):
SELECT * FROM demo_table2;
Step 6: Update demo_table1 data from demo_table2 based on ID match
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.
For example:
demo_table1 and demo_table 2 both has NAME column.
- demo_table1.ID: specifies ID column of demo_table1
- demo_table2.ID: specifies ID column of demo_table2
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;
View the content of demo_table1 again to see the updated table.
Output:
We can see the content is updated now based on ID values of demo_table2.