Monday, April 24, 2017

Deleting Rows Based on a Table Lookup or CTE

There will be times when you’ll want to delete rows from a table based on data in another table. For example, suppose you want to delete rows from the SalesStaff table for only those salespeople who had no sales in the preceding year. However, the SalesStaff table does not contain this information, but the vSalesPerson view does.

DELETE SalesStaff
WHERE StaffID IN
  (
    SELECT BusinessEntityID
    FROM Sales.vSalesPerson
    WHERE SalesLastYear = 0
  );