SQL Query to Update Columns Values With Column of Another Table

To update column values with the column values of another table, use the nested statement feature of SQL. Using the feature, we can select data from two tables, and update data in one table based on data from another table.

Using JOIN also allows to connection of two tables, and updates the column values of one table with the column of another table.

Here, we will use the easy method of nested statements to update the column values of a table with the column values of another table.

Let’s look the the SQL query to update column values with column values of another table.

First, we will create a demo database and two tables.

Query:

CREATE DATABASE w3wiki;
USE w3wiki;

CREATE TABLE student_details(
  stu_id VARCHAR(8),
  stu_name VARCHAR(20),
  stu_cgpa DECIMAL(4,2) );

CREATE TABLE Updated_CG(
stu_id VARCHAR(20),
updated_cg DECIMAL(4,2));

INSERT INTO student_details (stu_id, stu_name, stu_cgpa) VALUES
('40001', 'PRADEEP', 9.6),
('40002', 'ASHOK', 8.2),
('40003', 'PAVAN KUMAR', 7.6),
('40004', 'NIKHIL', 8.2),
('40005', 'RAHUL', 7.0);


INSERT INTO Updated_CG (stu_id, updated_cg) VALUES
('40001', 8.6),
('40002', 8.4),
('40003', 6.6),
('40004', 7.2),
('40005', 7.8);

SELECT * FROM student_details
SELECT * FROM Updated_CG

Output:

Update Columns Values With Column of Another Table Example

In this example, we will write a SQL query to update the old C.G.P.A in student_details table to new C.G.P.A   from the table Upadated_CG

Query:

UPDATE student_details 
SET stu_cgpa = (SELECT updated_cg 
FROM Updated_CG 
WHERE student_details.stu_id = Updated_CG.stu_id)

Updated Table: