SQL UPDATE with JOIN

Introduction

UPDATE statement in SQL is used to change data in tables and views, and UPDATE with JOIN is used to update multiple tables. This article teaches how to update data in a SQL Server table or view using the SQL UPDATE with JOIN statement. 

First of all, we are going to create a new database in SQL Server. You can still do this step if you already have an existing database.

Create a new Database

The following SQL query creates a new database and a few tables. Copy this query and execute it in Query Explorer or the command line. 

CREATE DATABASE OnkarSharma_UPDATE_JOIN
PRINT 'New Database ''OnkarSharma_UPDATE_JOIN'' Created'
GO

USE [OnkarSharma_UPDATE_JOIN]
GO

CREATE TABLE [dbo].[Employee] (
	EmployeeID INT IDENTITY (31100,1),
	EmployerID BIGINT NOT NULL DEFAULT 228866,
	FirstName VARCHAR(50) NOT NULL,
	LastName VARCHAR(50) NOT NULL,
	Email VARCHAR(255) NOT NULL,
	DepartmentID VARCHAR(100) NOT NULL,
	Age INT  NOT NULL,
	GrossSalary BIGINT NOT NULL,
	PerformanceBonus BIGINT,
	ContactNo VARCHAR(25),
	PRIMARY KEY (EmployeeID)
);

CREATE TABLE [dbo].[Restaurant](
	RestaurantId INT IDENTITY (51, 1),
	RestaurantName VARCHAR(MAX) NOT NULL,
	Email VARCHAR(100),
	City VARCHAR(100),
	Zipcode VARCHAR(6) NOT NULL,
	State VARCHAR(100) NOT NULL,
	Country VARCHAR(100) NOT NULL,
	PRIMARY KEY(RestaurantId)
);

CREATE TABLE [dbo].[tbl_Orders] (
	OrderId INT IDENTITY (108, 1) PRIMARY KEY,
	FoodieID INT,
	OrderStatus TINYINT NOT NULL, -- ==>> OrderStatus: 4: Cancelled; 3: Pending; 2: Processing; 1: Completed
	OrderDate DATETIME NOT NULL,
	ShippedDate DATETIME,
	RestaurantId INT NOT NULL,
);

CREATE TABLE [dbo].[tbl_OrderItems](
	OrderId INT NOT NULL,
	ItemId INT,
	MenuId INT NOT NULL,
	Quantity INT NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	BillAmount DECIMAL(5, 2) NULL,
	PRIMARY KEY (ItemId)
);

CREATE TABLE [dbo].[tbl_Menu] (
	MenuId INT IDENTITY (81, 1) PRIMARY KEY,
	FoodCategoryID INT NOT NULL,
	FoodName VARCHAR (255) NOT NULL,
	TypeofFood VARCHAR (100) NOT NULL,
	Price DECIMAL(6, 2) NOT NULL,
	Discount DECIMAL(5, 2) NOT NULL DEFAULT 0,
);

Next, you can insert data to these tables using the SQL INSERT statement or by adding data direct to the tables in SSMS.

Let's check our following tables by using the following queries.

1) To get the data from the "Employee" table, use the following query.

SELECT * FROM OnkarSharma_UPDATE_JOIN..Employee

2) To get the data from the "tbl_OrderItems" table, use the following query.

SELECT * FROM OnkarSharma_UPDATE_JOIN..tbl_OrderItems

 3) To get the data from the "tbl_Menu" table, use the following query.

SELECT * FROM OnkarSharma_UPDATE_JOIN..tbl_Menu

SQL Server UPDATE JOIN

DBAs use update queries in SQL Server to update an existing row in a table. A DBA may update all or some records based on the condition specified in the WHERE clause. With the help of UPDATE JOIN (Cross-Table Update), a DBA can perform various tasks such as updating the main table using an audit table (another table) in case of any mismatch, updating records in the main table based on multiple tables, etc.

Using SQL Server UPDATE JOIN, you can Update a table by joining two or more tables together.

Key Points

• SQL Server UPDATE JOIN is also known as a "cross-table" update.
• DBAs cannot update multiple tables simultaneously using SQL Server UPDATE JOIN.
• A single update statement cannot be used to update multiple columns from different tables.

Syntax

UPDATE <table_name>
SET <table_name>.<column_name> = <table_name1>.<column_name>, ...
FROM <table_name>
[ INNER | LEFT ] JOIN <table_name1> ON <join_predicate>
[ WHERE Condition(s) ]

In this syntax

• UPDATE: First, specify the table name <table_name> that you want to update. (Users can also use the SQL alias instead of the table name).
• SET: Next, specify the new value for the column of the updated table.
• FROM: In the FROM clause, re-specify the table you want to update. (And, use INNER or LEFT JOIN to join with other table using JOIN predicate).
• WHERE: Finally, specify the WHERE clause to update only specific rows. this is an optional argument.

Syntax (with table alias)

UPDATE A
SET A.<column_name> = B.<column_name>, ...
FROM <table_name> A
[ INNER | LEFT ] JOIN <table_name1> B ON <join_predicate>
[ WHERE Condition(s) ]

Note. Table alias can be used instead of the table_name in the UPDATE JOIN syntax.

Examples

The examples in this section demonstrate the functionality of UPDATE JOIN In SQL Server. Let's see.

1) To get the correct data back

Let’s create an audit table named "[dbo].[tbl_EmployeeAudit]" for demonstration.

SELECT * INTO [dbo].[tbl_EmployeeAudit]
FROM [dbo].[Employee]

Suppose the DBA wants to update an employee's email in the "Employee" table, but unfortunately, the DBA executed the update query without the WHERE clause.

UPDATE [dbo].[Employee]
SET Email = '[email protected]'

Now, DBA can restore all correct emails using the audit table with the help of the UPDATE JOIN to get back the correct data.

UPDATE Employee
SET Employee.Email = tbl_EmployeeAudit.Email
FROM Employee
INNER JOIN tbl_EmployeeAudit on Employee.EmployeeID = tbl_EmployeeAudit.EmployeeID

Use the following query to see the result.

SELECT * FROM [dbo].[Employee]

2) To calculate bill amount using multiple tables

With the help of the following query, you can calculate the bill amount for the ordered items by using UPDATE INNER JOIN.

UPDATE tbl_OrderItems
SET tbl_OrderItems.BillAmount = (tbl_OrderItems.Price * tbl_OrderItems.Quantity) - (tbl_OrderItems.Quantity * tbl_Menu.Discount)
FROM tbl_OrderItems
INNER JOIN tbl_Menu ON tbl_OrderItems.MenuId = tbl_Menu.MenuId

Use the following query to see the result.

SELECT * FROM OnkarSharma_UPDATE_JOIN..tbl_OrderItems

3) To update multiple columns from multiple tables

By using SQL Server UPDATE JOIN, you cannot update multiple tables simultaneously. An error message will appear if you try to update multiple columns from multiple tables.

UPDATE tbl_OrderItems
SET 
tbl_OrderItems.BillAmount = 0,
tbl_Menu.Discount = 0
FROM tbl_OrderItems
INNER JOIN tbl_Menu ON tbl_OrderItems.MenuId = tbl_Menu.MenuId

Now, to update multiple tables, you have to execute separate queries.

UPDATE tbl_OrderItems
SET tbl_OrderItems.BillAmount = 0
FROM tbl_OrderItems
INNER JOIN tbl_Menu ON tbl_OrderItems.MenuId = tbl_Menu.MenuId

UPDATE tbl_Menu
SET tbl_Menu.Discount = 0
FROM tbl_Menu
INNER JOIN tbl_OrderItems ON tbl_OrderItems.MenuId = tbl_Menu.MenuId

Use the following query to see the result.

SELECT * FROM OnkarSharma_UPDATE_JOIN..tbl_OrderItems
SELECT * FROM OnkarSharma_UPDATE_JOIN..tbl_Menu

See you in the next article, till then take care and be happy learning.

You may also visit my other articles.

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• INSERT INTO SELECT vs SELECT INTO In SQL Server
• Different Ways To Handle NULL In SQL Server
• DDL Triggers In SQL Server
• IIF Function In SQL Server

Conclusion

In this article, we have discussed the Concept of UPDATE JOIN In SQL with various examples.

Thanks for reading.