DELETE Query in SQL

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:

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.

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

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:

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:

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:

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.