Share via

  • Article

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