SQL Server UPDATE from a SELECT statement

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.