Subqueries in UPDATE, DELETE, and INSERT Statements
Subqueries can be nested in the UPDATE, DELETE, INSERT and SELECT data manipulation (DML) statements.
The following example doubles the value in the ListPrice column in the Production.Product table. The subquery in the WHERE clause references the Purchasing.ProductVendor table to restrict the rows updated in the Product table to just those supplied by VendorID 51.
USE AdventureWorks;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
(SELECT ProductID
FROM Purchasing.ProductVendor
WHERE VendorID = 51);
GO
Here is an equivalent UPDATE statement using a join:
USE AdventureWorks;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID AND pv.VendorID = 51;
GO
See Also
Concepts
Other Resources
Changing Data in a Database
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
SELECT (Transact-SQL)