Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Overview of the SQL Delete Statement

DZone's Guide to

Overview of the SQL Delete Statement

This article on the SQL Delete is a part of the SQL essential series on key statements, functions, and operations in SQL Server.

· Database Zone ·
Free Resource

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

This article on the SQL Delete is a part of the SQL essential series on key statements, functions, and operations in SQL Server.

To remove a row from a table is accomplished through a Data Manipulation Language, aka DML statement, using the delete keyword. The SQL delete operation is by far the simplest of all the DML commands. On execution of the delete command, we don't have to worry about getting any form of data from the table, and we don't have to worry about working with any data that we get back from the table(s). We just simply tell the database to delete a specific record, and it either does or it doesn't. It's that simple.

First, let's quickly review what an SQL delete statement looks like. We need to tell the database and table from where it should delete the data. It's a good idea to add a condition clause to set the scope of data deletion. Otherwise, it will delete everything in the table.

Let's take a look at our table and removing some records.

How to Delete Rows With No Where Clause

The following example deletes all rows from the Person.Person the table in the AdventureWorks2014 database. There is no restriction enforced on the SQL delete statement using a WHERE clause.

USE Adventureworks2014;
GO
DELETE FROM [Person].[Person];

How to Delete Rows With Where Clause

The following example deletes rows from the [Person].[Person] table in the AdventureWorks2014 database in which the value in the businessEntityID column is greater than 30,,000

USE Adventureworks2014;
GO
DELETE FROM [Person].[Person]
WHERE businessEntityID > 30000;

Note: An unfortunate mistake that may occur is to accidentally run a SQL Delete with no Where clause and inadvertently delete all of your data. To prevent this from happening, consider using the Execution guard feature in ApexSQL Complete, to warn against such potentially damaging actions, before you execute them. Learn more: Execution alerts

How to Delete Rows Using Top With Where Clause

The following example deletes 50 random rows from the Person.Person table in the AdventureWorks2014 database. The value in the BusinessEntityID must be in between 30,000 and 40,000.

USE Adventureworks2014;
GO
DELETE TOP(50) FROM [Person].[Person]
WHERE BusinessEntityID between 30000 and 40000

Note: The when the TOP (n) clause is used with the SQL Delete statement and any DML statement (i.e. Select, Insert, Delete and Update), the operation is performed on a random selection of a number of rows specified in the Top clause.

How to Delete Duplicate Rows

In the real world, we tend to gather data from different sources; it's not uncommon to have duplicate records. One approach to the duplicate problem is first to identify where the duplicates have occurred. And run a select query on those columns.

EATE TABLE tb_spaceused
(database_name       NVARCHAR(128), 
 database_size       VARCHAR(18), 
 [unallocated space] VARCHAR(18), 
 reserved            VARCHAR(18), 
 data                VARCHAR(18), 
 index_size          VARCHAR(18), 
 unused              VARCHAR(18)
);
INSERT INTO tb_spaceused
EXEC sp_msforeachdb 
     @command1 = "use ? exec sp_spaceused  @oneresultset = 1";

SELECT *
FROM tb_spaceused
order by database_name

The following example uses the PARTITION BY argument to partition the query result set by all the columns of tb_spaceused table. The Row_Number (), a window function, which means it operates over an ordered set. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the entire columns tb_spaceused table.

WITH CTE
     AS (SELECT *, 
                ROW_NUMBER() OVER(PARTITION BY database_name, 
                                               database_size, 
                                               [unallocated space], 
                                               reserved, 
                                               data, 
                                               index_size, 
                                               unused
                ORDER BY database_name
                        ) AS Row_Num
         FROM tb_spaceused)
     SELECT *
     FROM CTE
     WHERE Row_Num <> 1;

Replacing the Select statement with a Delete removes all the duplicates of the table.

WITH CTE
     AS (SELECT *, 
                ROW_NUMBER() OVER(PARTITION BY database_name, 
                                               database_size, 
                                               [unallocated space], 
                                               reserved, 
                                               data, 
                                               index_size, 
                                               unused
                ORDER BY database_name
                        ) AS Row_Num
         FROM tb_spaceused)
     --SELECT *
     --FROM CTE
     --WHERE Row_Num <> 1;
 DELETE FROM CTE
     WHERE Row_Num <> 1;

How to Delete Rows Using SQL Sub-Queries

In the following example, the rows in one table are deleted based on data in another table. In the examples, the rows from the SalesPersonQuotaHistory table are deleted based on the SalesYTD column of the SalesPerson table.

DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(
    SELECT BusinessEntityID
    FROM Sales.SalesPerson
    WHERE SalesYTD > 4900000.00
); 
GO

How to Delete Rows Using SQL Joins

In this section, we will use the SQL Delete statement to delete the data from the Adeventureworks2014 database. Deleting data, at first sight, sound trivial, but once we get into a large database design things might not be same and easy anymore.

In many cases, the tables are related via a primary and foreign key relationship. In the following example, we can see a use of joins to delete the data from the Sales.SalesPersonQuotaHistory.

DELETE sq
FROM Sales.SalesPersonQuotaHistory sq
     INNER JOIN Sales.SalesPerson sp ON sq.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 4500000.00;  
GO

How to Delete Rows From a Remote Table Using Linked Servers and OpenQuery

The following example uses the SQL delete statement to delete rows from a remote table using the linked server named, hqdbt01. Then query the remote table using four-part object naming convention to delete the rows from the remote table.

DELETE
FROM [hqdbt01].AdventureWorks2014.[HumanResources].[Shift]
WHERE ShiftID = 2;

The following example, the remote table is queried by specifying the OPENQUERY rowset function along with the delete command.

DELETE OPENQUERY (hqdbt01, 'SELECT *
FROM AdventureWorks2014.HumanResources.Department  
WHERE DepartmentID = 18');

How to Delete Rows Using SSMS

Using the SQL Server Management Studio (SSMS), Graphical User Interface (GUI) to delete rows involves a manual search. In reality, it will be much easier and quicker to delete records with a SQL query.

Let's go ahead and locate the table to use a SQL delete statement, in this case, table dbo.cities is selected. Now, right-click and choose Edit Top 200 rows. This option opens up a query designer window. Next, right-click the window and select Execute SQL and write a new query that will delete rows from the dbo.cities table.

In the result pane, make sure that SELECT Statement is pulling up the correct targeted records before start deleting rows. Select the rows and right-click the rows and choose Delete to remove the rows from the table.

Summary

Thus far, we've seen many different ways use the SQL delete statement to remove data. But, there is a list of the consideration to be followed while using the delete statement, and it as follows:

  1. It is always recommended to start with a SELECT statement before you delete anything to make sure that you're targeting the correct records. So the delete statement is used to delete rows from a table using the where clause to select only the rows to be deleted.
  2. Always use a unique identifier to locate the rows that you need to delete.
  3. To delete all the rows in a table, always use TRUNCATE TABLE. TRUNCATE TABLE which is faster than a SQL delete statement and it uses fewer system and transaction-log resources.
  4. By default, DELETE statements induce an exclusive (X) lock on the table, and it holds the lock until the transaction completes.
  5. An exclusive lock prevents other transaction from modifying the data; read operations are allowed with the use of NOLOCK hint or read uncommitted isolation level.
  6. It is recommended to specify the TABLOCK hint in the delete statement. This process allows page de-allocation and associated space available for reuse by other objects in the database.
  7. It is good practice to create a clustered index on the heap table before executing a delete statement.
  8. Although very simple and very powerful, and the result of a Delete statement is destructive. Deleted rows cannot be easily recovered.

Note: To recover deleted rows see SQL Server disaster recovery — How to quickly recover data lost due to an Inadvertent delete operation

That's all for now. Hope you enjoyed reading this article on the SQL delete statement. If you have any questions, feel free to comment below.

The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.

Topics:
database tutorial ,sql delete statement ,sql tutorial ,overview of sql ,how to delete rows ,database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}