DELETE Query in SQL

Video Tutorial
FREE
 UPDATE , DELETE  thumbnail
This video belongs to
DBMS Course - Master the Fundamentals and Advanced Concepts
16 modules
Certificate
Topics Covered

When we work with a database table, we might come across records that are no longer required and hence, need to be deleted. We use the DELETE query in SQL to remove unwanted records from a given table. We can make use of the WHERE clause to delete only specific rows which satisfy a given condition.

DELETE Syntax

The basic syntax for a Delete Query in SQL is as follows:

  • table_name is the name of the table where you want to delete data.
  • condition specifies which rows should be removed. If you omit the condition, be careful, as it will delete all the rows in the table!

SQL DELETE Example

Let us first create an employee table that has the Employee ID, First Name, Last Name, Work Location, and Designation of each employee using the below query.

We then enter some records into the empty employee table.

Once the above data is inserted, we run the below SELECT query to display all the records in our employee table.

Output:

EmployeeIDFirstNameLastNameWorkLocationDesignation
62549JohnDoeBangaloreSoftware Engineer
82743PriyaSharmaHyderabadDB Administrator
58461RajKumarGurugramTechnical Architect
95462RaviBangaloreManager
25947ShreyaPChennaiSoftware Engineer
42650JaneScottHyderabadAnalyst

Suppose in the above table, we need to delete the records of an employee with ID 58461, the SQL query will look like this:

Output:

We get the following output when we run the SELECT query again on the employee table.

EmployeeIDFirstNameLastNameWorkLocationDesignation
62549JohnDoeBangaloreSoftware Engineer
82743PriyaSharmaHyderabadDB Administrator
95462RaviBangaloreManager
25947ShreyaPChennaiSoftware Engineer
42650JaneScottHyderabadAnalyst

Notice that the record of the employee having ID as 58461 is deleted from the employee table.

Deleting Single Record

Let's first introduce a demo table to use as our example. This table represents a simple user list in a database.

Table: Users

UserIDFirstNameLastNameAge
1EmmaSmith30
2LiamJohnson24
3OliviaWilliams28
4NoahBrown33
5AvaJones22
6WilliamGarcia27
7SophiaMiller26

Now, let's proceed with an example where we aim to delete a specific record.

When managing data in a database, you may occasionally need to delete a single record. This could be because the information is no longer relevant, incorrect, or for privacy reasons.

Example

Suppose we want to delete the record for a user named "William Garcia". To achieve this, we use a Delete Query with a condition that matches "William Garcia".

Output

After executing the above query, the Users table would be updated to look like this:

UserIDFirstNameLastNameAge
1EmmaSmith30
2LiamJohnson24
3OliviaWilliams28
4NoahBrown33
5AvaJones22
7SophiaMiller26

Notice that the record for "William Garcia" has been removed.

Verification

To verify that the record has been successfully deleted, you can run a SELECT query to search for the deleted record:

This query should return no results, indicating that the record for "William Garcia" has indeed been deleted from the Users table. Verification is an important step to ensure the accuracy of your data manipulation operations.

Deleting Multiple Records

In database management, there might be instances where you need to delete more than one record at a time. This could be to remove outdated information, duplicate entries, or records that match specific criteria. Using the Users table from our previous example, let's explore how to delete multiple records in one operation.

Example

Imagine we want to delete records of all users who are 30 years old or older. This action targets multiple entries in our table based on a condition related to the users' age.

Output

Executing the above query will remove all users from the Users table who are 30 years old or older. The updated Users table will look like this:

UserIDFirstNameLastNameAge
2LiamJohnson24
3OliviaWilliams28
5AvaJones22
6WilliamGarcia27
7SophiaMiller26

As you can see, the records for Emma Smith (30), Olivia Williams (28), Noah Brown (33), and assuming we had not already deleted William Garcia in the previous example, would be removed based on our condition.

Verification

To ensure the deletion was successful, we can perform a verification step by executing a query to find records of users who are 30 years old or older:

This query should return no results, confirming that all records matching our criteria have been successfully removed from the Users table. Verification helps ensure that our database accurately reflects the deletions we intended to make.

Delete All of the Records

There are scenarios where you might need to completely clear out a table in your database. This could be due to the need for resetting data for testing purposes, preparing the table for new data, or simply because the data is no longer needed. Deleting all records from a table is a powerful action and should be performed with caution.

Example

To delete all records from our Users table without removing the table itself, we use the DELETE query without specifying a WHERE condition. Here's how you would do it:

This command tells the database to remove every record from the Users table, leaving the table structure (columns and their data types) intact but with no data.

Output

After executing the above query, the Users table would be completely empty, as shown below:

UserIDFirstNameLastNameAge
(No records found)

The table still exists, but it contains no records.

Caution It's important to approach this operation with caution. Deleting all records from a table is irreversible without a backup. Always ensure that you have a backup of your data or that you are certain the data is no longer needed before executing such a command.

Verification

To verify that the table is indeed empty, you can run a SELECT query to attempt to retrieve any records:

This query should return an empty result set, indicating that the table no longer contains any records. This verification step confirms that the deletion operation was successful and the table is ready for new data or further actions.

Conclusion

  1. The DELETE query in SQL allows for precise control over data removal, from a single entry to multiple records, based on specific conditions.
  2. Utilizing the WHERE clause effectively tailors the deletion process, ensuring only the intended records are removed, thereby preventing accidental data loss.
  3. Post-deletion verification is crucial. Running SELECT queries post-deletion confirms the accuracy of the operation, ensuring data integrity.
  4. The power of the DELETE query, especially when used without a WHERE clause to remove all records, underscores the importance of caution. Always verify the need for such an operation and consider data backups.
  5. Regular use of DELETE queries, when appropriately applied, aids in database maintenance, keeping the data relevant, and efficient, which is essential for the smooth operation of any database-driven application.