SQL Server UPDATE With JOIN

Introduction

In this tutorial, I am going to explain the concept of SQL Server UPDATE JOIN. This detailed article will cover the following topics,

1. Introduction
2. UPDATE JOIN In SQL Server
3. Examples
4. Conclusion

 First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.

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,
);

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 of the 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 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 the email of an employee 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 at the same time. If you try to update multiple columns from multiple tables, an error message will appear.

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,

Conclusion

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

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.