The article covers Update from Select statements using different methods specified below:
UPDATE from SELECT: Join Method
UPDATE from SELECT: The MERGE statement
UPDATE from SELECT: Subquery Method
The Update statement in SQL is used to modify an existing row in a table. It can modify all table rows or specific rows satisfying where predicate.
Sample query to update all table data
UPDATE table SET col1 = [Value1] , col2 = [Value2]
Sample query to update specific rows
UPDATE table SET col1 = [Value1] , col2 = [Value2] where [col]= [Value3]
To understand the article, let’s create sample data using T-SQL below.
Use DemoDatabase;
GO
CREATE TABLE dbo.Customers
( CustomerId INT
PRIMARY KEY IDENTITY(1, 1) NOT NULL,
Name VARCHAR(100) NOT NULL,
LastName VARCHAR(100) NULL,
PostCode VARCHAR(100) NULL,
City VARCHAR(100) NULL)
GO
CREATE TABLE Address(
[AddressId] [int]
PRIMARY KEY IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NULL,
[PostCode] [varchar](100) NULL,
[City] [varchar](100) NULL)
GO
INSERT INTO Customers (Name, LastName)
VALUES
(N'Rajendra', N'Gupta'),
(N'Mohan', N'Kumar'),
( N'Kusum', N'Agarwal'),
( N'Himanshu', N'Upadhyay'),
( N'John', N'Peter')
GO
INSERT INTO Address
(CustomerId, PostCode, City)
VALUES
(1, N'122102', N'Gurugram'),
(2, N'302032', N'Jaipur'),
(3, N'400029', N'Mumbai'),
(4, N'600001', N'Chennai')
SELECT * FROM Customers
SELECT * FROM Address
The customer table has NULL values for columns [PostCode] and [City].
Let’s see how to update these columns values from the table [Address] data.
UPDATE from SELECT: Join Method
This method uses joins between different tables to update data in the required table. For example, the following query joins tables [Customers] and [Address] on the [CustomerID] column. For all the matching rows, it updates the column [City], [PostCode] on the Customers table from the corresponding values of [City], [PostCode] on the [Address] table.
UPDATE C
SET
C.City=A.City,
C.PostCode=A.PostCode
FROM Customers C
INNER JOIN Address A
ON C.CustomerID = A.CustomerID
The [PostCode] and [City] are NULL for the last row because it does not have a matching row in the [Address] table for customer id 5.
UPDATE from SELECT: The MERGE statement
The Merge statement can update a target table from the reference table for the matched and unmatched rows.
For example, look at the following T-SQL; it uses the target table [Customers] and reference table [Address]. For the matched rows, we update the City and PostCode of the target table.
MERGE Customers AS C
USING(SELECT * FROM Address) AS A
ON C.CustomerID = A.CustomerID
WHEN MATCHED THEN
UPDATE SET
C.City=A.City,
C.PostCode=A.PostCode;
SELECT * FROM Customers
Note: The merge statement must use a semicolon ( ; ) as a terminator
UPDATE from SELECT: Subquery Method
We can use a subquery, an internal query inside a DML statement. The DML can Select, Update, Insert or delete statements.
The following query uses a subquery to find the customer's city and postcode using the subquery. The subquery fetches data from the [Address] table on the matching rows.
UPDATE Customers
SET Customers.City=(SELECT Address.City
FROM Address
WHERE Address.CustomerID = Customers.CustomerID)
UPDATE Customers
SET Customers.PostCode=(SELECT Address.PostCode
FROM Address
WHERE Address.CustomerID = Customers.CustomerID)
Conclusion
This article covered the Update from Select statement in SQL Server for performing data updates. You can choose any method per your requirement and consider performance implications.
Comments