Mahmoud ElMansy: knowledge meant to be free

Delete Statment in SQL

DELETE (Transact-SQL)
Removes rows from a table or view.

in this post i will start series about deleting data from Table Or View.
i will discuse and show simple and complex delete statments
A. Using DELETE with no WHERE clause

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Using DELETE on a set of rows

USE AdventureWorks2008R2;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

C. Using DELETE on the current row of a cursor

USE AdventureWorks2008R2;
GO
DECLARE complex_cursor CURSOR FOR
SELECT a.BusinessEntityID
FROM HumanResources.EmployeePayHistory AS a
WHERE RateChangeDate <>
(SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory AS b
WHERE a.BusinessEntityID = b.BusinessEntityID) ;
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

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension
USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
GO

E. Using DELETE with the TOP clause

USE AdventureWorks2008R2;
GO
DELETE TOP (2.5) PERCENT
FROM Production.ProductInventory;
GO

F. Using DELETE with the OUTPUT clause

USE AdventureWorks2008R2;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;

--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [ROWS IN TABLE] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

G. Using OUTPUT with from_table_name in a DELETE statement

USE AdventureWorks2008R2;
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