Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.