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.


70 views0 comments