Delen via

  • Artikel

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 BusinessEntity 1540.

USE AdventureWorks2008R2;
GO 
UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID 
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

Here is an equivalent UPDATE statement using a join:

USE AdventureWorks2008R2;
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 BusinessEntityID = 1540;
GO