DbSchema Tutorial | SQL DELETE STATEMENT

Publish on DbSchema Blog >>>

SQL (Structured Query Language) is a language used to interact with relational databases. It allows you to create, read, update, and delete data stored in a database. In this tutorial, we will focus on the SQL DELETE statement, which is used to remove rows from a database table.

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table. It is essential to be careful when using this statement as once the data is deleted, it cannot be recovered.

Syntax

The basic syntax of the DELETE statement is as follows:

DELETE FROM table_name WHERE condition;

• table_name: The name of the table from which you want to delete records.
• condition: The condition that identifies which rows to delete. If you omit the WHERE clause, all rows in the table will be deleted.

Delete a Single Record

To delete a single record from a table, you can use the WHERE clause to specify the condition that identifies the row.

Example

Let’s consider the following Students table:

StudentID	FirstName	LastName
1	John	Doe
2	Jane	Doe
3	Sam	Smith

To delete the record of the student with StudentID = 2, you can execute the following query:

DELETE FROM Students WHERE StudentID = 2;

Result

After executing the query, the Students table will look like this:

StudentID	FirstName	LastName
1	John	Doe
3	Sam	Smith

Explanation

the DELETE statement was used to remove a single record from the Students table where the StudentID was 2. After executing the query, the record with StudentID 2 was successfully removed, leaving only the records with StudentID 1 and 3.

Delete Multiple Records

You can delete multiple records from a table by specifying a condition in the WHERE clause that matches multiple rows.

Example

Using the modified Students table from the previous example:

StudentID	FirstName	LastName
1	John	Doe
3	Sam	Smith

To delete all records with LastName = ‘Doe’, you can execute the following query:

DELETE FROM Students WHERE LastName = 'Doe';

Result

After executing the query, the Students table will look like this:

StudentID	FirstName	LastName
3	Sam	Smith

Explanation

the DELETE statement was used to remove all records from the Students table where the LastName was ‘Doe’. After executing the query, both records with LastName ‘Doe’ were removed, leaving only the record with LastName ‘Smith’.

Remove All Rows from a Table

To delete all rows from a table, you can execute the DELETE statement without a WHERE clause.

Example

Using the modified Students table from the previous example:

StudentID	FirstName	LastName
3	Sam	Smith

To delete all records from the Students table, you can execute the following query:

DELETE FROM Students;

Result

After executing the query, the Students table will be empty.

Explanation

Here, the DELETE statement was executed without a WHERE clause, which resulted in the removal of all records from the Students table, leaving the table empty.

Delete Rows with WHERE Clause

The WHERE clause is used to specify the condition that identifies the rows to delete.

Example

Let’s consider the following Employees table:

EmployeeID	FirstName	LastName
101	Alice	Johnson
102	Bob	Smith
103	Carol	Lee

To delete the record of the employee with EmployeeID = 102, you can execute the following query:

DELETE FROM Employees WHERE EmployeeID = 102;

Result

After executing the query, the Employees table will look like this:

EmployeeID	FirstName	LastName
101	Alice	Johnson
103	Carol	Lee

Explanation

In this case, the DELETE statement was used to remove the record from the Employees table where the EmployeeID was 102. After executing the query, only the records with EmployeeID 101 and 103 remained in the Employees table.

Delete Rows with No WHERE Clause

As mentioned earlier, if you omit the WHERE clause in a DELETE statement, all rows in the table will be deleted. It is highly recommended to always use a WHERE clause to specify the condition for deletion, to avoid deleting all the data in the table accidentally.

Example

Let’s consider the following Employees table:

EmployeeID	FirstName	LastName
101	Alice	Johnson
102	Bob	Smith
103	Carol	Lee

To delete all records from the Employees table, you can execute the following query:

DELETE FROM Employees;

Result

After executing the query, the Employees table will be empty.

Explanation

In this scenario, the DELETE statement was executed on the Employees table without a WHERE clause. As a result, all records from the Employees table were deleted, leaving the table empty.

Delete Rows Using TOP with WHERE Clause

In SQL Server, you can use the TOP clause with the DELETE statement to limit the number of rows that will be deleted.

Syntax

DELETE TOP (number) FROM table_name WHERE condition;

• number: The number of rows to delete.

Example

Let’s consider the following Orders table:

OrderID	ProductName	Quantity
1	Product A	10
2	Product B	20
3	Product C	30
4	Product D	40

To delete the top 2 orders with Quantity greater than 20, you can execute the following query:

DELETE TOP (2) FROM Orders WHERE Quantity > 20;

Result

After executing the query, the Orders table will look like this:

OrderID	ProductName	Quantity
1	Product A	10
2	Product B	20

Explanation

The DELETE statement with the TOP clause was used to remove the top 2 records from the Orders table where the Quantity was greater than 20. After executing the query, only the records with Quantity 10 and 20 remained in the Orders table.

Delete Duplicate Rows

To delete duplicate rows from a table, you can use a common table expression (CTE) with the ROW_NUMBER() function.

Example

Let’s consider the following Products table:

ProductID	ProductName
1	Product A
2	Product B
3	Product A
4	Product C

To delete the duplicate rows with the same ProductName, you can execute the following query:

WITH CTE AS (
  SELECT *,
  ROW_NUMBER() OVER(PARTITION BY ProductName ORDER BY ProductID) AS RowNumber
  FROM Products
)
DELETE FROM CTE WHERE RowNumber > 1;

Result

After executing the query, the Products table will look like this:

ProductID	ProductName
1	Product A
2	Product B
4	Product C

Explanation

Here, a common table expression (CTE) with the ROW_NUMBER() function was used to identify and delete the duplicate rows from the Products table. After executing the query, only one record for each ProductName remained in the Products table.

Delete Rows Using SQL Sub-queries

You can use a subquery in the WHERE clause of a DELETE statement to specify the condition for deletion.

Example

Let’s consider the following Orders table:

OrderID	CustomerID	ProductID
1	101	1
2	102	2
3	103	3
4	104	4

And the Customers table:

CustomerID	CustomerName
101	Alice
102	Bob
103	Carol
104	Dave

To delete all orders placed by the customer with CustomerName = ‘Bob’, you can execute the following query:

DELETE FROM Orders
WHERE CustomerID = (
  SELECT CustomerID
  FROM Customers
  WHERE CustomerName = 'Bob'
);

Result

After executing the query, the Orders table will look like this:

OrderID	CustomerID	ProductID
1	101	1
3	103	3
4	104	4

Explanation

The DELETE statement with a subquery was used to remove all records from the Orders table where the CustomerName was ‘Bob’ in the Customers table. After executing the query, the record with CustomerID 102 was removed from the Orders table.

Delete Rows Using SQL Joins

You can use a JOIN clause in a DELETE statement to specify the condition for deletion based on the relationship between two tables.

Example

Let’s consider the following Orders table:

OrderID	CustomerID	ProductID
1	101	1
2	102	2
3	103	3
4	104	4

And the Customers table:

CustomerID	CustomerName
101	Alice
102	Bob
103	Carol
104	Dave

To delete all orders placed by the customer with CustomerName = ‘Carol’, you can execute the following query:

DELETE Orders
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerName = 'Carol';

Result

After executing the query, the Orders table will look like this:

OrderID	CustomerID	ProductID
1	101	1
2	102	2
4	104	4

Explanation

In this case, the DELETE statement with a JOIN clause was used to remove all records from the Orders table where the CustomerName was ‘Carol’ in the Customers table. After executing the query, the record with CustomerID 103 was removed from the Orders table.

Difference Between SQL Server TRUNCATE and DELETE

The TRUNCATE and DELETE statements are used to remove rows from a table, but there are some differences between them.

Feature	TRUNCATE	DELETE
Speed	Faster because it does not log the deletion of individual rows.	Slower because it logs the deletion of each row.
Locking	Locks the entire table.	Locks the rows that are being deleted.
Where Clause	Cannot be used with a WHERE clause.	Can be used with a WHERE clause to delete specific rows.
Triggers	Does not activate triggers.	Activates triggers.
Space Reclamation	Releases the space occupied by the table immediately.	Does not release the space immediately.
Identity Column Reset	Resets the identity column value to its initial value.	Does not reset the identity column value.
Transaction Log Space	Uses less transaction log space.	Uses more transaction log space.
Foreign Key Constraints	Cannot be used on tables with foreign key references.	Can be used on tables with foreign key references.

Common Mistakes

• Not Using a WHERE Clause: This is a common mistake that can lead to the deletion of all rows in the table. Always use a WHERE clause to specify the condition for deletion unless you intend to delete all rows.

• Incorrect WHERE Clause: Specifying an incorrect condition in the WHERE clause can lead to the deletion of unintended rows. Always double-check the WHERE clause before executing the DELETE statement.

FAQs

• What is the difference between DELETE and DROP?

  • The DELETE statement is used to delete rows from a table, whereas the DROP statement is used to delete the entire table from the database.

• Can the deleted rows be recovered?

  • Once the rows are deleted using the DELETE statement, they cannot be recovered. It is recommended to create a backup of the table before executing the DELETE statement.

• Does the DELETE statement remove the table structure?

  • No, the DELETE statement only removes the rows from the table. The table structure remains unchanged.

Practice Questions

1. Write a SQL query to delete all orders placed by a customer with CustomerID = 105.

2. Write a SQL query to delete all employees with a salary greater than $50,000.

3. Write a SQL query to delete all products with a stock quantity less than 10.

4. Write a SQL query to delete all orders placed in the month of January 2023.

Conclusion

In this tutorial, we have covered the SQL DELETE statement in detail, including its syntax, usage, and examples. We have also discussed common mistakes and frequently asked questions.
Mastering the DELETE statement requires regular practice and real-world application. This guide is a starting point, but hands-on experience will solidify your understanding.

_Happy Querying !!_