SQL UPDATE with JOIN: How it Works

Consistent data is important for reliability and integrity in relational databases. When modifying existing data across related tables, we use the UPDATE statement with the JOIN clause to achieve data consistency. The UPDATE with JOIN operation is supported in SQL Server and is useful in ensuring accurate data synchronization across tables.

The SQL JOIN operation allows for more complex updates beyond single-table operations through foreign keys shared across the different tables. The UPDATE with JOIN operation is useful for developers and database administrators since it improves efficiency and reduces risks of data inconsistencies. 

Before you get started, I recommend taking DataCamp’s Introduction to SQL Server course to get familiar with how SQL Server databases operate. This tutorial is written specifically with SQL Server in mind. Also, check out our tutorial, Introduction to SQL Joins, to learn how to use JOIN when working with multiple tables in SQL.

How to Use SQL UPDATE with JOIN

In SQL Server, the UPDATE with JOIN operation allows you to update records in one table based on matching data from another. This technique is particularly useful when synchronizing data across multiple tables.

Using SQL UPDATE with INNER JOIN

The INNER JOIN in SQL retrieves rows/records with matching data in both tables. When used with the UPDATE statement, an INNER JOIN ensures that only those records in the target table that have corresponding matches in the joined table are updated.

The typical structure of the UPDATE with INNER JOIN involves specifying the target table that needs to be updated and defining the columns to be updated. The structure also includes using the INNER JOIN to link it with another table based on a common column to update only the required records.

INNER JOIN example

Assume you are managing a retail database where you need to update a Sales table to synchronize with changes in the Customer table. A customer has recently changed their preferred contact method, and you would want these changes to be reflected in the two tables. You will use the UPDATE with INNER JOIN to ensure that only the sales records related to customers who have updated their contact information are modified without affecting other rows in the data.

-- Update the Sales table based on the new preferred contact methods in the Customers table
UPDATE s
SET s.ContactMethod = c.PreferredContactMethod
FROM Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
WHERE c.CustomerID IN (3, 7, 9);

UPDATE with INNER JOIN best practices

When using UPDATE with INNER JOIN, consider the following best practices for efficient queries.

• Use Indexes on Join Columns: Ensure the columns used in the JOIN condition are indexed to optimize performance, speeding up the query execution.

• Limit Scope of the Update: Use the WHERE clause to ensure only the relevant rows are updated and improve query performance.

Using SQL UPDATE with LEFT JOIN

A LEFT JOIN is used to retrieve all records from the left (first) table and the matching records from the right (second) table. A query with LEFT JOIN will return NULL results from the right table if there are no matching results. When used with the UPDATE statement, the LEFT JOIN can update records even when there is no matching data in the second table, which is useful for filling in missing data. Check out our SQL Joins Cheat Sheet if you are looking to review SQL joins.

LEFT JOIN example

Consider the scenario where you are managing a database for an online retail store. The Customers table contains a list of all customers, while the Orders table contains details of orders placed by some of these customers. You will use the LEFT JOIN to retrieve and update the order details for all customers, including those who haven't placed an order yet. This method will fill in the missing data for the customers who have no orders placed with a NULL or specified value.

Our example with LEFT JOIN is going to be a little different because LEFT JOIN returns NULL values if no matching records are found in the right table. For this reason, it is important to find out how to handle these NULL values. You can handle the NULL values by setting a default value in the columns to be updated. Using the COALESCE() function, the LEFT JOIN will include a condition for a default value for the missing data. The following query shows how to set a default value for NULL values.

-- Update all customers to set default values where OrderStatus and OrderDate are NULL
UPDATE c
SET 
    c.OrderStatus = COALESCE(o.OrderStatus, 'No Orders'),
    c.OrderDate = COALESCE(o.OrderDate, '2023-01-01')   
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Select from the updated Customers table
SELECT * 
FROM Customers;

You can also use the COALESCE() or IFNULL() functions to handle NULL values that involve calculations before updating the columns. Check out our COALESCE() SQL Function tutorial to learn more.

UPDATE with LEFT JOIN best practices

When using UPDATE with LEFT JOIN, consider the following best practices for efficient queries.

• Use Explicit Column References: Instead of using SELECT *, explicitly specify the columns you want to update to make your query clearer and avoid unintended updates.

• Use Indexes on Join Columns: Ensure that the columns used in the JOIN condition are indexed to improve the performance of the JOIN operation and the overall query execution time.

SQL Update with Join Performance Considerations

Understanding the performance implications when using UPDATE with JOIN in SQL is important for ensuring that your queries run efficiently. The performance of these operations can be influenced by several factors, including the size of the tables used, the type of join used, and whether appropriate indexes are in place.

Indexes and query speed

Indexing the columns involved in the join condition is important for speeding up the update process. When you index the columns appropriately, the operation will only locate the matching rows during the join, reducing the time required for the table scan.

Impact of large datasets

When working with large datasets, query executions may be slower due to resource-intensive operations. Consider the following best practices to mitigate performance issues with large datasets. Also, try out the SQL Server for Database Administrators skill track if you will be designing or maintaining databases regularly as part of your work. 

• Batch Updates: Break the updates into smaller batches to reduce the load on the database and update the records efficiently.

• Limit the Cope of the Update: When you use the WHERE clause, you specify the rows to update, reducing the processing time of the queries.

Common Mistakes and How to Avoid Them

There are some common mistakes that you may encounter when using UPDATE with JOIN in SQL Server. These mistakes might lead to performance issues or updating unintended data. The following are some of these mistakes and how to avoid them.

Unintended updates

If you fail to specify the join condition properly, the UPDATE with JOIN will update more records than intended. This mistake will update an entire table instead of the required subset. To avoid this error, always ensure you use a precise JOIN condition to update the required rows.

Cartesian products

A Cartesian product occurs when the join condition is incorrect or missing, which causes the query to match every row from the left table with every row from the right table. This issue might slow down the query operation and produce inaccurate results. To prevent this mistake, always ensure you have the JOIN condition properly defined. Also, ensure you indicate the correct columns to apply the join condition.

SQL UPDATE with JOIN Alternatives

Although the UPDATE with JOIN is a powerful method for updating records across related tables, there are alternative methods for achieving the update. The following are some options.

Subqueries

Subqueries are an important alternative to JOIN when you want to perform updates based on a simple condition. With subqueries, you will avoid multiple complex tables by specifying the condition in the subquery.

The following example shows how to use a subquery to update the Sales table with the latest CustomerStatus from the Customers table.

-- Update the CustomerStatus in the Sales table
UPDATE Sales
-- Set the CustomerStatus in Sales to corresponding value from the Customers table
SET CustomerStatus = (
    -- Fetch CustomerStatus from the Customers table for the matching CustomerID
    SELECT CustomerStatus 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
)
-- Update rows in Sales where a matching CustomerID exists in the Customers table
WHERE EXISTS (
    -- Subquery to check if a matching CustomerID exists in the Customers table
    SELECT 1 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
);

MERGE statement

The MERGE statement is a versatile SQL command that allows you to perform UPDATE, INSERT, or DELETE operations in a single query based on comparing two tables. The MERGE statement is useful when you need to synchronize data between two tables, handling matching and non-matching rows.

Using the MERGE statement is helpful because it provides a concise method for performing multiple operations in one query. The syntax of the MERGE statement is as follows.

-- Merge data from the source table into the target table
MERGE INTO target_table AS target
USING source_table AS source
    -- Define the common key between the source and target tables
    ON target.common_column = source.common_column
-- When a match is found in both tables based on the common key
WHEN MATCHED THEN
    -- Update the target table's column with the corresponding value from the source table
    UPDATE SET target.column = source.column
-- When a row exists in the source table but not in the target table
WHEN NOT MATCHED THEN
    -- Insert the new data into the target table
    INSERT (column1, column2) 
    VALUES (source.column1, source.column2)
-- When a row exists in the target table but not in the source table
WHEN NOT MATCHED BY SOURCE THEN
    -- Delete the row from the target table
    DELETE;

Conclusion

Understanding how to use the UPDATE with JOIN in SQL Server is important for efficient cross-table data updates in relational databases. This technique lets you update records in one table based on matching data in another, ensuring data consistency and synchronization across related tables. Applying these techniques and best practices can enhance your database operations, ensuring accurate, efficient, and reliable data management. 

If you want to advance your SQL skills, I recommend checking DataCamp’s SQL Server Fundamentals skill track to improve your data analysis skills. The Writing Functions and Stored Procedures in SQL Server course will also help you learn efficient data manipulation in SQL Server. If you are preparing for your SQL interview, I recommend reading our blog post, 20 Top SQL Joins Interview Questions to help you prepare. Finally, if you are looking to apply your SQL skills in a career as a data engineer, consider also taking our Data Engineer in Python career track, complete with a certification at the end.