This article contains basic SQL DELETE statements that beginners can use to delete data from their database tables.
Delete a Single Row
Here’s a basic example of the SQL DELETE statement.
DELETE FROM Owners
WHERE OwnerId = 5;In this case we delete the row where the OwnerId column has a value of 4.
The DELETE statement starts with DELETE FROM, followed by the table name (i.e. the table that contains the data you want to delete).
In some Database Management Systems (DBMSs), the FROM keyword is optional, but it’s a good idea to include it for better portability (in case you need to run the same code on a different DBMS).
You should always include a WHERE clause, unless you want to delete all rows from the table.
Yes, you read that right. Omitting the WHERE clause will delete all rows in the table.
Most DBMSs have various other options that you can use with the DELETE statement, but the ones listed here are the most commonly used ones.
Note that the DELETE statement deletes the whole row. You can’t delete the value of an individual column within a row. To do this, use the SQL UPDATE statement.
Example
In this example, we delete data from a table.
First of all, let’s see what’s in the table.
SELECT * FROM Owners;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Stallone | (489) 591-0408 | NULL | | 4 | Boris | Biden | (349) 611-8908 | [email protected] | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
OK let’s delete owner number 5, then select the table again.
DELETE FROM Owners
WHERE OwnerId = 5;
SELECT * FROM Owners;Result:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Stallone | (489) 591-0408 | NULL | | 4 | Boris | Biden | (349) 611-8908 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
We can see that the last row has been deleted as specified.
Foreign Keys
You may get an error if you try to delete data that’s referenced by a foreign key in another table. This is because the table you’re trying to delete data from is the parent table in a relationship. The table with the foreign key (the child table) relies on data in the parent table (i.e. data that you’re trying to delete).
Whether you get an error or not will depend on how the foreign key was configured. Most DBMSs support various options for dealing with this situation. Such options could include, raising an error, cascading the delete to the foreign key table (i.e. deleting the row from the child table), setting the foreign key to NULL, or setting it to its default value.
Here’s an example of trying to delete a row that is referenced by a foreign key.
DELETE FROM Owners
WHERE OwnerId = 3;Result:
Msg 547, Level 16, State 0, Line 1 The DELETE statement conflicted with the REFERENCE constraint "FK_Pets_Owners". The conflict occurred in database "PetHotel", table "dbo.Pets", column 'OwnerId'. The statement has been terminated.
In this case, an error was raised, and the row wasn’t deleted.
In this example, the Owners.OwnerId column is the primary key of this table. Another table called Pets has an OwnerId column that references this primary key column, and in this case, there’s at least one row that is referencing owner number 3.
If I really wanted to delete this row, I would need to update data in the child table so that no rows point to this owner. Either that, or modify the foreign key so that it uses a different option when delete operations occur on the primary key. Modifying a foreign key could be an undesirable option, depending on what you need to do. Depending on your DBMS, it may also require dropping the existing key and recreating it.
In any case, this error is a good thing, because it alerts us to an issue that we will have if we delete this row. It helps enforce referential integrity.
Caution! Forgetting the WHERE Clause
The DELETE statement can be a very dangerous statement. If you omit the WHERE clause, you will delete all rows in the table.
Fortunately, you may be saved by foreign key constraints if you try to delete data from the parent table of a relationship.
But what if it’s not. What if no errors are raised by your delete statements?
Let’s find out!
Let’s run another DELETE statement, but this time we’ll forget to include the WHERE clause. Also, this time we’ll run it against a table that is not a parent in a relationship.
First, let’s see what’s in the table.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
OK, so we’ve got eight pets. We can actually see that three pets are owned by owner number 3 – the owner we tried to delete in the previous example. This is why we got the error.
Anyway, let’s go ahead and run our DELETE statement without the WHERE clause, then check the table again
DELETE FROM Pets;
SELECT * FROM Pets;Result:
(8 rows affected) (0 rows affected)
The (8 rows affected) part means that eight rows were deleted.
The (0 rows affected) part means that no rows were selected (because there are no rows in the table.
Oops!
There may be times where you do in fact need to delete all rows in the table. Depending on the size of the table, this statement will be all you need.
If you need to delete a larger table, there’s also TRUNCATE TABLE, which removes all rows from a table or specified partitions of a table, without logging the individual row deletions (the DELETE statement logs these deletions).
Therefore, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Example of using TRUNCATE TABLE:
TRUNCATE TABLE Owners;Note that this may not work if the table is being referenced by a foreign key constraint, even if there are no child rows. In such cases, DELETE may work instead.