The DELETE reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to remove existing data in one table in the database. Today, I would like to review the different ways to remove data using the AdventureWorks Sample database supplied by Microsoft. Each example has a SELECT statement to identify the number of rows affected before executing the DELETE.
1 – One way to purge data from a table is to use the DELETE statement without a where clause. Only use this syntax when the table size is small since every deleted row is recorded in the TRANSACTION LOG when the Recovery Model is FULL. A better way to clear all data from a table is the TRUNCATE TABLE statement which does minimal logging.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<span style="color: #008000;">-- MS sample database USE [AdventureWorks2008R2]; GO -- Create copy of person table in stage SELECT * INTO STAGE.Person FROM Person.Person; GO -- How many contacts (19972) SELECT COUNT(*) AS Total FROM STAGE.Person; GO -- Clear the newly created table DELETE FROM STAGE.Person; GO </span> |
2 – The simplest form of the DELETE statement removes a single row from the table using one WHERE clause expression. The example below removes the first non-employee from the person table in the staging schema.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000;">-- Add data back to copy INSERT INTO STAGE.Person SELECT * FROM Person.Person; GO -- People who are employees (273) SELECT * FROM STAGE.Person A WHERE A.PersonType = 'EM' ORDER BY A.BusinessEntityID; GO -- Delete the 274th row DELETE FROM STAGE.Person WHERE BusinessEntityID = 274; GO </span> |
3 – A more complex form of the DELETE statement removes several rows from the table using several WHERE clause expressions. The following example finds all people who are employees or vendors. All other persons are removed from the staging table.
|
1 2 3 4 5 6 |
<span style="color: #008000;">-- Find all employees and vendors (429) SELECT * FROM STAGE.Person A WHERE A.PersonType = 'EM' OR A.PersonType = 'VC'; GO -- Remove people who are not employees or vendors (19543) DELETE FROM STAGE.Person WHERE PersonType NOT IN ('EM', 'VC'); GO </span> |
4 – The most complex form of the DELETE statement remove several rows using several join tables and where clauses to get the resulting purge set. Transaction SQL supports an non-standard FROM key word that makes the query simpler. However, this form is not ANSI compitable. If you are writing code that might have to be executed on another database platform in the future, do not use this form. The ANSI Standard syntax can be used with a NOT IN sub-query to achieve the same results.
Please see both the ANSI and TSQL versions of the UPDATE statement below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<span style="color: #008000;">-- Reload the table DELETE FROM STAGE.Person; INSERT INTO STAGE.Person SELECT * FROM Person.Person; SELECT COUNT(*) AS Total FROM STAGE.Person; GO -- Find all Sales people in the United States (12) SELECT * FROM HumanResources.Employee AS e INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE E.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US'; GO -- ANSI Standard Syntax DELETE FROM Stage.Person WHERE Person.BusinessEntityID NOT IN ( SELECT e.BusinessEntityID FROM HumanResources.Employee AS e INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID WHERE e.JobTitle LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) = 'US' ); GO -- TSQL Enhanced Syntax DELETE FROM Stage.Person FROM Stage.Person AS s LEFT JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = s.BusinessEntityID LEFT JOIN Person.Address AS a ON a.AddressID = bea.AddressID LEFT JOIN Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID LEFT JOIN HumanResources.Employee AS e ON s.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle IS NULL OR ISNULL(e.JobTitle, ' ') NOT LIKE '%SALES%' OR (ISNULL(e.JobTitle, ' ') LIKE '%SALES%' AND UPPER(sp.CountryRegionCode) <> 'US'); GO </span> |
5 – It is very important to consider what happens to the TRANSACTION LOG with DML statements. I am going to introduce an advance technique which changes the recovery model to simple and uses implicit transactions to execute check point to clear the transaction log after a batch size of data is deleted. The selected bactch size for this example is 100 rows but can be set to any number.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<span style="color: #008000;">-- Reload the table DELETE FROM STAGE.Person; INSERT INTO STAGE.Person SELECT * FROM Person.Person; SELECT COUNT(*) AS Total FROM STAGE.Person; GO -- Allow log to truncate at end of block, check point ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE; GO -- Declare row counter DECLARE @r INT; SET @r=1; -- While there is data, nibble off rows WHILE (@r > 0) BEGIN DELETE TOP (100) FROM STAGE.Person WHERE BusinessEntityID > 273; SET @r=@@ROWCOUNT END; GO -- Log all future transactions ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL; GO -- How many employees (273) SELECT COUNT(*) AS Total FROM STAGE.Person; GO </span> |
In summary, working with databases is vital in the information age. That said, removing data is a daily task that developers and administrators perform. Knowing the different variantions of the DELETE statement will help you select the correct syntax.
Hello. impressive job. I did not imagine this. This is a remarkable story. Thanks!