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 geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

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.