SQL | UPDATE with JOIN

In SQL, the UPDATE with JOIN statement is a powerful tool that allows updating one table using data from another table based on a specific JOIN condition. This technique is particularly useful when we need to synchronize data, merge records, or update specific columns in one table by referencing related records from another table.

In this article, we will explore how to use the SQL UPDATE JOIN statement effectively with detailed examples, outputs, and best practices to optimize performance.

What is SQL UPDATE with JOIN?

The UPDATE with JOIN statement enables us to modify records in a table using a JOIN operation. It combines data from two or more tables based on a matching condition (e.g., INNER JOIN, LEFT JOIN) and updates the specified columns in one table. SQL UPDATE JOIN could be used to update one table using another table and join condition.

Syntax

UPDATE target_table
SET target_table.column_name = source_table.column_name,
    target_table.column_name2 = source_table.column_name2
FROM target_table
INNER JOIN source_table
ON target_table.column_name = source_table.column_name
WHERE condition;

Key Terms

• target_table: The table whose records you want to update.
  
• source_table: The table containing the data you want to use for the update.
  
• SET: Specifies the columns in the target table that will be updated.
  
• INNER JOIN: Ensures only matching rows from both tables are considered.
  
• ON: The condition that specifies how the tables are related.
  
• WHERE: An optional clause to filter which rows to update.

Example: SQL UPDATE with JOIN Using Two Tables

Let us assume we have two tables Geeks1 and Geeks2, each containing data about different attributes. In this example, we will update Geeks1 by joining it with Geeks2 based on a common column col1.

Table Geeks1

Table Geeks2

We have table Geeks2 which has two rows where Col 1 is 21 & 31 and We want to update col2 and col3 in table Geeks1 using values from table Geeks2 where col1 matches in both tables. Specifically, we will update the rows where col1 is 21 and 31

Query:

UPDATE Geeks1  
SET col2 = Geeks2.col2,  
col3 = Geeks2.col3  
FROM Geeks1  
INNER JOIN Geeks2 ON Geeks1.col1 = Geeks2.col1  
WHERE Geeks1.col1 IN (21, 31);

Output

Explanation:

• We are updating col2 and col3 in table Geeks1.
  
• We use an INNER JOIN to match rows from Geeks1 and Geeks2 where col1 is the same.
  
• The WHERE clause filters the rows for col1 = 21 and col1 = 31.

SQL UPDATE with JOIN Using LEFT JOIN

Sometimes you may need to update records in the target table even if there is no match in the source table. In such cases, we can use LEFT JOIN.

Syntax

UPDATE target_table
SET target_table.column_name = source_table.column_name
FROM target_table
LEFT JOIN source_table
ON target_table.column_name = source_table.column_name;

Example

Let’s say we want to update the Geeks1 table but only change col2 where a match exists; otherwise, set it to a default value.

UPDATE Geeks1
SET col2 = ISNULL(Geeks2.col2, 0)
FROM Geeks1
LEFT JOIN Geeks2
ON Geeks1.col1 = Geeks2.col1;

Output

Explanation:

• Rows with no match in Geeks2 have col2 set to 0 (default value).
  
• Rows with matches are updated with values from Geeks2.

Conclusion

The SQL UPDATE with JOIN statement is a powerful technique for updating data in one table using values from another table based on specific conditions. By using INNER JOIN or LEFT JOIN, we can perform targeted updates while maintaining data integrity. This article covered practical examples, syntax, and key performance tips to help us efficiently use UPDATE JOIN in SQL. Whether we're synchronizing tables, merging data, or cleaning up records, mastering this approach is essential for database administrators and developers.