SQL Server Update Join: How to Update Data in Two or More Tables

Welcome Dev, in this article, we will discuss SQL server update join, a powerful technique that allows you to update data in multiple tables simultaneously. If you are a developer, database administrator, or data analyst who works with relational databases, this article will help you master the update join statement in SQL Server.

What is SQL Server Update Join?

SQL Server update join is a SQL statement that combines the update and join operations to update data in two or more tables. The update join statement allows you to update columns in one table based on the values in another table. The update join statement can be useful when working with denormalized tables or when you need to update data in a master table that has foreign key constraints.

Here’s the basic syntax of the SQL Server update join statement:

Let’s break down the syntax into its components:

• UPDATE: The keyword that starts the update statement.
• table1: The table that you want to update.
• SET: The keyword that indicates which column(s) to update.
• column1: The name of the column to update in table1.
• value1: The new value for column1.
• FROM: The keyword that indicates the second table to join.
• table2: The second table to join.
• WHERE: The keyword that indicates the join condition.
• table1.column2: The join column in table1.
• table2.column2: The join column in table2.

Now that we have seen the basic syntax of the SQL Server update join statement, let’s look at some examples to understand how it works in practice.

Examples of SQL Server Update Join

Example 1: Update data in one table based on another table

Suppose you have two tables:

In this example, the Customers table has a primary key column (CustomerID), and the Orders table has a foreign key column (CustomerID) that references the CustomerID column in the Customers table. Suppose you want to update the LastName column in the Customers table with the last name of the customer who made the latest order. You can do this with the following SQL Server update join statement:

UPDATE CustomersSET LastName = Orders.LastNameFROM CustomersINNER JOIN (SELECT CustomerID, LastNameFROM CustomersINNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDWHERE OrderDate = (SELECT MAX(OrderDate)FROM Orders)) AS Orders ON Customers.CustomerID = Orders.CustomerID

Let’s break down the SQL Server update join statement:

• The UPDATE keyword starts the SQL statement.
• The Customers table is the table to be updated.
• The SET keyword specifies the column to update (LastName) and the source of the new value (Orders.LastName).
• The FROM keyword specifies the second table to join (Orders).
• The INNER JOIN clause specifies the join condition between the Customers and Orders tables.
• The subquery inside the INNER JOIN clause selects the customer ID and last name of the customer who made the latest order.
• The WHERE clause specifies the condition for the latest order date.

After you execute this SQL Server update join statement, the LastName column in the Customers table will be updated as follows:

Example 2: Update data in multiple tables simultaneously

Suppose you have three tables:

In this example, the Customers table has a primary key column (CustomerID), the Orders table has a foreign key column (CustomerID) that references the CustomerID column in the Customers table, and the OrderDetails table has a foreign key column (OrderID) that references the OrderID column in the Orders table. Suppose you want to update the LastName column in the Customers table with the last name of the customer who made the latest order, and the ProductID column in the OrderDetails table with a new value. You can do this with the following SQL Server update join statement:

UPDATE CustomersSET LastName = Orders.LastNameFROM CustomersINNER JOIN (SELECT CustomerID, LastNameFROM CustomersINNER JOIN Orders ON Customers.CustomerID = Orders.CustomerIDWHERE OrderDate = (SELECT MAX(OrderDate)FROM Orders)) AS Orders ON Customers.CustomerID = Orders.CustomerIDUPDATE OrderDetailsSET ProductID = 4FROM OrderDetailsINNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderIDWHERE Orders.CustomerID = (SELECT CustomerIDFROM CustomersWHERE LastName = Orders.LastName)

Let’s break down the SQL Server update join statement:

• The first UPDATE statement updates the LastName column in the Customers table, as we saw in the previous example.
• The second UPDATE statement updates the ProductID column in the OrderDetails table.
• The FROM clause specifies the second table to join (Orders).
• The INNER JOIN clause specifies the join condition between the OrderDetails and Orders tables.
• The WHERE clause specifies the condition for the customer whose last name matches the last name in the Orders table.

After you execute this SQL Server update join statement, the LastName column in the Customers table will be updated as follows:

And the ProductID column in the OrderDetails table will be updated as follows:

Frequently Asked Questions

What is a join in SQL Server?

A join in SQL Server is a SQL statement that combines rows from two or more tables based on a related column between them. The join operation allows you to retrieve data from multiple tables in a single query. There are several types of joins in SQL Server, such as inner join, left join, right join, and full outer join.

What is an update statement in SQL Server?

An update statement in SQL Server is a SQL statement that modifies one or more rows in a table. The update statement allows you to change the values of existing data in a table. The basic syntax of the update statement is as follows:

What is a primary key in SQL Server?

A primary key in SQL Server is a column or a combination of columns that uniquely identifies each row in a table. The primary key constraint ensures that the values in the primary key column(s) are unique and not null. The primary key is used to establish relationships between tables and enforce referential integrity.

What is a foreign key in SQL Server?

A foreign key in SQL Server is a column or a combination of columns that references the primary key of another table. The foreign key constraint ensures that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table. The foreign key is used to establish relationships between tables and enforce referential integrity.

What is denormalization in SQL Server?

Denormalization in SQL Server is the process of optimizing a database for read performance by adding redundant data or breaking the normalization rules. Denormalization can improve query performance by reducing the number of joins required to retrieve data. However, denormalization can also lead to data inconsistencies and increase the complexity of the data model.