SQL Query to Update Columns Values With Column of Another Table

The SQL UPDATE statement is one of the most powerful tools for modifying data in a relational database. Sometimes, we need to update column values in one table based on values from another table. This can be done using various methods such as JOIN or nested SELECT statements.

In this article, we will explore both of these methods, providing us with practical examples and best practices to help you master SQL updates across multiple tables.

What is the SQL UPDATE Statement?

The SQL UPDATE statement is used to modify the existing data in one or more columns of a table. It is a very common operation when managing database records, and it allows us to change the values of specific columns based on certain conditions. When working with multiple tables, SQL provides several ways to update columns in one table using the data from another table.

Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

Methods to Update Column Values Using Data from Another Table

There are several methods to achieve this, such as using nested SELECT statements or JOIN operations. Both techniques allow us to retrieve data from one table and use it to update corresponding values in another table, ensuring data consistency and reducing manual intervention.

1. Using Nested SELECT Statements

One of the most common ways to update column values from another table is by using nested SELECT statements. This method allows us to select data from the second table and use it to update the first table. The nested SELECT query is placed inside the SET clause of the UPDATE statement.

Example: Update Column Values Using Nested SELECT

Let’s consider two tables: student_details and Updated_CG. In the student_details table, we store student information, including their CGPA (Cumulative Grade Point Average), and in the Updated_CG table, we have the updated CGPA values for each student

Table1: Student_details

CREATE TABLE student_details(
  stu_id VARCHAR(8),
  stu_name VARCHAR(20),
  stu_cgpa 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);

SELECT * FROM student_details

Output

Table2: Updated_CG

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

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 Updated_CG

Output

In this example, we want to update the CGPA in the student_details table using the updated CGPA values from the Updated_CG table. We can do this with a nested SELECT statement.

Query:

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

Output

Explanation:

In this example, the CGPA values in the student_details table are updated based on the corresponding stu_id values from the Updated_CG table. The nested SELECT statement retrieves the updated CGPA for each student and updates the record accordingly

2. Using SQL JOIN to Update Column Values from Another Table

Another method to update a column in one table using data from another table is by using an SQL JOIN. This method is particularly useful when we need to update values based on a relationship between two tables.

Example: Update Column Values Using SQL JOIN

In the following example, we will update the CGPA in the student_details table using the Updated_CG table, this time using an INNER JOIN.

Query:

UPDATE student_details
SET stu_cgpa = Updated_CG.updated_cg
FROM student_details
INNER JOIN Updated_CG
ON student_details.stu_id = Updated_CG.stu_id;

Output

Explanation:

In this case, the INNER JOIN is used to link the student_details table with the Updated_CG table based on the stu_id column. The UPDATE statement then sets the stu_cgpa in the student_details table to the corresponding updated_cg value from the Updated_CG table

Conclusion

The SQL UPDATE statement is an essential part of database management. Updating columns with values from another table is a common use case that can be achieved using either nested SELECT statements or JOINs. By using these methods, we can efficiently update multiple records and ensure that our data remains consistent across tables. By mastering these SQL techniques, we can confidently handle bulk updates, maintain data consistency, and ensure smooth database operations.