SQL DELETE Statement Explained with Examples
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 theWHEREclause, 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
WHEREclause to specify the condition for deletion unless you intend to delete all rows. -
Incorrect WHERE Clause: Specifying an incorrect condition in the
WHEREclause can lead to the deletion of unintended rows. Always double-check theWHEREclause before executing theDELETEstatement.
FAQs
-
What is the difference between DELETE and DROP?
- The
DELETEstatement is used to delete rows from a table, whereas theDROPstatement is used to delete the entire table from the database.
- The
-
Can the deleted rows be recovered?
- Once the rows are deleted using the
DELETEstatement, they cannot be recovered. It is recommended to create a backup of the table before executing theDELETEstatement.
- Once the rows are deleted using the
-
Does the DELETE statement remove the table structure?
- No, the
DELETEstatement only removes the rows from the table. The table structure remains unchanged.
- No, the
Practice Questions
-
Write a SQL query to delete all orders placed by a customer with
CustomerID= 105. -
Write a SQL query to delete all employees with a salary greater than $50,000.
-
Write a SQL query to delete all products with a stock quantity less than 10.
-
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 !!_
