How to use SQL DELETE to delete separate or multiple entries

SQL DELETE is the simplest command for removing an entry from a table. It lets you use a WHERE condition. This condition is optional, but if you omit it, the entire table will be emptied.

What is SQL Delete?

When working with a table, there will always be instances where an entry becomes obsolete and should no longer be listed in your data record. To remove an entry like this, the Structured Query Language provides the SQL DELETE statement. This command allows you to delete one or more specific entries from the table. To ensure that only the desired data is removed, the use of the WHERE statement is crucial. If you omit this condition, all entries will be removed from the table. Therefore, it is essential to use this command with great caution.

Syntax and function

In the syntax of SQL DELETE, the specification is implied by the addition of the WHERE clause. Therefore, you always form the SQL command in this way:

DELETE FROM name_of_table 
WHERE condition;

In the first line, you start the command and specify the table where the deletion will occur. The second line is optional. and is where you define the condition a row must meet to be deleted. If you skip this line, the system will delete all rows in the table.

Remove one or more entries

The easiest way to explain how SQL DELETE works is with the help of an example. Let us create a fictitious table called “CustomerList”. This table contains different entries for customers of a company, including a customer number, name, and location. Here’s what the table looks like:

If you now want to delete the customer “Johnson” from your list, apply the following:

DELETE FROM Customer_list
WHERE Customer number = 1377;

Since only the customer “Johnson” has the customer number “1377”, the resulting table is:

Alternatively, you could have selected the name “Johnson” as a condition under WHERE.

If you want to delete several customers, this works according to a similar principle. In our example, we could remove all entries with the location Los Angeles. This would be the appropriate code:

DELETE FROM Customer_list
WHERE Location = 'Los Angeles';

Since two entries have this value, the resulting table is:

Remove all entries from a table

If you omit the WHERE condition when executing SQL DELETE, all entries will be removed. The table itself will still exist, but it will be empty afterward. For our example, the appropriate command would be:

DELETE FROM Customer_list;

You should therefore use this command with great caution.

Delete an entire table with DROP TABLE

To remove the entire table, the DROP TABLE command is the appropriate choice. Here is its syntax:

DROP TABLE name_of_table;

In our example, the corresponding code would be:

DROP TABLE Customer_list;

Similar commands to SQL DELETE

An alternative to SQL DELETE is the TRUNCATE TABLE command. However, this can only be used to remove all entries in a table at once. It does not support a WHERE condition. You can create a new table using SQL CREATE TABLE. To prevent accidental and irreversible data loss, regular backups are recommended. For this, you can use SQL BACKUP DATABASE.