--------------------------------------------------------------------------- A. Using DELETE with no WHERE clause USE AdventureWorks; GO DELETE FROM Sales.SalesPersonQuotaHistory; GO --------------------------------------------------------------------------- B. Using DELETE on a set of rows USE AdventureWorks; GO DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00; GO --------------------------------------------------------------------------- C. Using DELETE on the current row of a cursor USE AdventureWorks; GO DECLARE complex_cursor CURSOR FOR SELECT a.EmployeeID FROM HumanResources.EmployeePayHistory AS a WHERE RateChangeDate <> (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory AS b WHERE a.EmployeeID = b.EmployeeID) ; OPEN complex_cursor; FETCH FROM complex_cursor; DELETE FROM HumanResources.EmployeePayHistory WHERE CURRENT OF complex_cursor; CLOSE complex_cursor; DEALLOCATE complex_cursor; GO --------------------------------------------------------------------------- D. Using DELETE based on a subquery and using the Transact-SQL extension -- SQL-2003 Standard subquery USE AdventureWorks; GO DELETE FROM Sales.SalesPersonQuotaHistory WHERE SalesPersonID IN (SELECT SalesPersonID FROM Sales.SalesPerson WHERE SalesYTD > 2500000.00); GO -- Transact-SQL extension USE AdventureWorks; GO DELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh INNER JOIN Sales.SalesPerson AS sp ON spqh.SalesPersonID = sp.SalesPersonID WHERE sp.SalesYTD > 2500000.00; GO --------------------------------------------------------------------------- E. Using DELETE with the TOP clause USE AdventureWorks; GO DELETE TOP (2.5) PERCENT FROM Production.ProductInventory; GO --------------------------------------------------------------------------- F. Using DELETE with the OUTPUT clause USE AdventureWorks; GO DELETE Sales.ShoppingCartItem OUTPUT DELETED.* ; --Verify all rows in the table have been deleted. SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem; GO output ShoppingCartItemID ShoppingCartID Quantity ProductID ------------------ -------------- -------- --------- 2 14951 3 862 5 20621 7 874 4 20621 4 881 (3 row(s) affected) Rows in Table ------------- 0 (1 row(s) affected) --------------------------------------------------------------------------- G. Using OUTPUT with from_table_name in a DELETE statement USE AdventureWorks GO DECLARE @MyTableVar table ( ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL, ProductModelID int NOT NULL, PhotoID int NOT NULL); DELETE Production.ProductProductPhoto OUTPUT DELETED.ProductID, p.Name, p.ProductModelID, DELETED.ProductPhotoID INTO @MyTableVar FROM Production.ProductProductPhoto AS ph JOIN Production.Product as p ON ph.ProductID = p.ProductID WHERE p.ProductModelID BETWEEN 120 and 130; --Display the results of the table variable. SELECT ProductID, ProductName, ProductModelID, PhotoID FROM @MyTableVar ORDER BY ProductModelID; GO ---------------------------------------------------------------------------