Summary:
From this post you will learn how & when to use DELETE statement in SQL.
About DELETE Statement: Removes one or more rows from a table or view in SQL Server. DELETE statement can be used broadly in 6 categories to perform row deletion.
1. Using DELETE to discard entire rows in a table:
The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because a WHERE clause is not used to limit the number of rows deleted
DELETE FROM Sales.SalesPersonQuotaHistory;
GO
2. Using the WHERE clause to delete a set of rows i.e. limit the number of rows:
The following example deletes all rows from the ProductCostHistory table in the AdventureWorks2012 database in which the value in the StandardCost column is more than 5000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 5000.00;
GO
3. Using a cursor to determine the row to delete
The following example deletes a single row from the EmployeePayHistory table in the AdventureWorks2012 database using a cursor named complex_cursor. The delete operation affects only the single row currently fetched from the cursor.
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
4. Using joins and subqueries to data in one table to delete rows in another table
The following examples show two ways to delete rows in one table based on data in another table. In both examples, rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database are deleted based on the year-to-date sales stored in the SalesPerson table. The first DELETE statement shows the ISO-compatible subquery solution, and the second DELETE statement shows the Transact-SQL FROM extension to join the two tables.
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
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;
Using TOP to limit the number of rows deleted
When a TOP (n) clause is used with DELETE, the delete operation is performed on a random selection of n number of rows. The following example deletes 20 random rows from the PurchaseOrderDetail table in the AdventureWorks2012 database that have due dates that are earlier than July 1, 2006.
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
5. Deleting Rows From a Remote Table
5. (a) Deleting data from a remote table by using a linked server
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'AdventureWorks2012';
GO
-- Specify the remote data source using a four-part name
-- in the form linked_server.catalog.schema.object.
DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department
WHERE DepartmentID > 16;
GO
5. (b) Deleting data from a remote table by using the OPENQUERY function
The following example deletes rows from a remote table by specifying the OPENQUERY rowset function. The linked server name created in the previous example is used in this example.
DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName
FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO
5. (c) Deleting data from a remote table by using the OPENDATASOURCE function
The following example deletes rows from a remote table by specifying the OPENDATASOURCE rowset function. Specify a valid server name for the data source by using the format server_name or server_name\instance_name.
DELETE FROM OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 17;'
6. Capturing the results of the DELETE statement
6 (a). Using DELETE with the OUTPUT clause
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
6 (b). Using OUTPUT with <from_table_name> in a DELETE statement
The following example deletes rows in the ProductProductPhoto table in the AdventureWorks2012 database based on search criteria defined in the FROM clause of the DELETE statement. The OUTPUT clause returns columns from the table being deleted, DELETED.ProductID, DELETED.ProductPhotoID, and columns from the Product table. This is used in the FROM clause to specify the rows to delete.
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
