What Is the DELETE Statement in SQL?

This article will explain the use of the DELETE statement, one of the primary methods of removing existing records from your database. First we will run through the syntax, followed by some easy-to-understand examples.

The DELETE statement in SQL is extremely useful. It allows you to remove any obsolete or “bad” data from your database tables. You should exercise caution when using the DELETE statement, as the operation is not reversible. There will be no dialog box asking you to confirm the operation. Once you run a DELETE query, the data will be gone – forever!

The DELETE statement is known as a data manipulation command. This article covers the other commands of this type, which are INSERT and UPDATE.

Let’s explore the syntax of SQL DELETE.

SQL DELETE Syntax

The syntax of the DELETE statement is as follows:

DELETE FROM  [WHERE ]

The table you would like to delete data from comes after the DELETE statement.

The square brackets indicate that this WHERE clause is entirely optional. But in practice, you’ll use this clause almost all the time – if you do not include a WHERE clause, all of the data from the table will get deleted.

That’s why it’s always a good idea to use SELECT to view the data you would like to delete. When the result from your SELECT query equals the data you intend to delete, simply replace the SELECT in your query with the DELETE statement. Every other part of your query can remain the same. This will ensure that you do not accidentally delete some vital data by mistake.

That concludes our look at the SQL DELETE syntax. It’s time to look at some practical examples, which will demonstrate some different uses of the DELETE statement and touch on some permissions issues you may face.

SQL DELETE Permissions

Before reviewing our examples, it is important to note that (depending on what SQL dialect you use) you may have to change user permissions so you can modify records with the DELETE statement.

By default, MySQL Workbench starts in safe mode; you cannot use UPDATE or DELETE without a WHERE condition.

For example, when using MySQL you will encounter the message: “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences → SQL Editor and reconnect”.

To rectify this, you have two options.

1. Change your settings like so:
   1. Go to Edit →
   2. Click the SQL Editor tab and uncheck the Safe Updates check box.
   3. Click Query → Reconnect to Server.
   4. Execute your SQL query.
2. Or you can run the following query:

   SET SQL_SAFE_UPDATES = 0;

After completing either of these options, you should be able to use DELETE to remove the data from your tables.

Now, let’s move on to the examples!

SQL DELETE Examples

Simple DELETE

Imagine we’re maintaining a database of employees and we’ve just got word that some of our employees have left the company. We’re going to remove them from our employees table. Let’s take a look at the current state of this table:

Imagine we had to remove George Harrison and Ringo Starr from our table. What are our options?

We should make sure of the employee_id column values before we remove these records. After we did that, the DELETE query would look like this:

DELETE FROM employees
WHERE employee_id = 3 OR employee_id = 4

This would successfully remove George and Ringo from our table.

You can also specify a single condition that would capture both of these records at once. In this case, as both George and Ringo are over 40 years old, we could use the age column:

DELETE FROM employees
WHERE age > 40

Executing this query will leave us with the following records in our employees table.

Our records have been successfully deleted! If you want to see more DELETE examples, check out this cookbook article, which shows you how to delete a single or multiple rows of data.

Let’s next consider a more advanced example that uses a subquery to delete data.

Subquery DELETE

You can also delete data from your tables using subqueries. A subquery is when one query is used as part of another. You can learn more about subqueries by reading this beginner-friendly article. Let’s look at a scenario where we can use a DELETE with a subquery.

We’ll use our employees table again:

We also have a sales_team table that contains records for everyone in the company’s sales department:

We’ve just received the terrible news that the sales team is being let go. It’s our job to update the employees and sales_team tables.

I’ll demonstrate how a subquery can help us in this particular scenario. Here’s the query:

DELETE FROM employees
WHERE employee_id 
IN (SELECT id
FROM sales_team)

Ok, let’s break this down.

The query in round brackets (i.e. the subquery) returns all id values from the sales_team table:

SELECT id FROM sales_team

This query gives us the values 1 and 3.

Let’s look at the DELETE part of the main query:

DELETE FROM employees
WHERE employee_id 
IN...

Notice the WHERE clause. In this part of the query, we are looking to delete records where the employee_id is in the results of our subquery. Remember the subquery evaluated to 1 and 3. So, this query evaluates to:

DELETE FROM employees
WHERE employee_id 
IN (1,3)

Suppose we execute the whole query and use the SELECT query below to view the results:

SELECT employee_id, first_name, last_name, age
FROM employees

This is what we get:

There we have it! The sales team was successfully removed from the employees table. You can see how useful a subquery was for this use case.

This has been a slightly more advanced use case. When discussing the DELETE statement, it is worth mentioning the TRUNCATE and DROP statements:

• TRUNCATE is faster than DELETE, as it does not scan the table before removing records. Because of this fact, it cannot be used with the WHERE condition or tell you how many rows were deleted when the operation is complete.
• The DROP TABLE command will remove everything to do with that table: the table definition and all related indexes, triggers, and constraints.

To learn more about these two methods of removing data, read this article about how to use subqueries with INSERT, UPDATE, and DELETE. You can also check out this this article that discusses the difference between DELETE, TRUNCATE, and DROP.

Deleting Primary Key and Foreign Key Constraints

One more thing we must discuss is the primary key (PK) and foreign key (FK) constraints. When working on smaller examples like the ones shown in this article, you will not have to worry about PK and FK constraints. But if you work with a large existing database, you will definitely encounter them.

When deleting data from a table, you may also want to remove any constraints associated with the data in that table. Check out the following examples if this applies to you:

• This example shows you how to delete a primary key.
• This example shows you how to delete a foreign key constraint.

Keep Your Data Relevant with SQL DELETE

That concludes our comprehensive look at the SQL DELETE statement. We’ve demonstrated its syntax and explored potential uses of this command through several examples.

If you’d like a more comprehensive SQL education, check out our A-Z SQL learning track that teaches you the basics and then moves onto more advanced topics.