SQLite - Delete Data
The DELETE statement can be used to delete data in a table.
The WHERE clause can be used in order to specify exactly which rows should be deleted.
You can delete all rows, some rows, or none, depending on the filtering conditions applied by the WHERE clause.
A Simple Delete
First, let's find an artist to delete:
sqlite> SELECT * FROM Artists;ArtistId ArtistName Bio-------- -------------------- ----------------------------------------1 Joe Satriani2 Steve Vai3 The Tea Party4 Noiseworks5 Wayne Jury6 Mr Percival7 Iron Maiden8 Atmasphere Australian jazz band centred around poly9 Ian Moss10 Magnum11 Strapping Young Lad12 Slayer13 Primus14 Pat Metheny15 Frank Gambale16 Mothers of Invention17 The Wiggles
OK, let's delete artist number 12 (Slayer).
Here's the simple delete statement that deletes that record from the Artists table:
DELETE FROM Artists
WHERE ArtistId = 12;
So, after running that, we can verify that the record was in fact deleted with a SELECT statement:
sqlite> SELECT * FROM Artists WHERE ArtistId = 12;sqlite>
(No results).
Deleting Data with a Foreign Key Constraint
If you attempt to delete a record that is referenced by a foreign key, you should get an error. You will need to delete the foreign key records first, before deleting the primary key record.
For example, artist ID 17 (from the Artists table) happens to be referenced by two records in the Albums table (see the ArtistId column):
sqlite> SELECT * FROM Albums WHERE ArtistId = 17;AlbumId AlbumName ReleaseDate ArtistId-------- -------------------- ------------ ------------5 Yummy Yummy 1994 1713 Big Red Car 1995 17
So if we try to delete artist 17 from the Artists table (which contains the primary key), we should get an error.
Here's the delete statement:
DELETE FROM Artists
WHERE ArtistId = 17;
And here's the result:
sqlite> DELETE FROM Artists...> WHERE ArtistId = 17;Error: FOREIGN KEY constraint failedsqlite>
So, we need to delete the foreign key records before we delete the primary key.
If you didn't get a FOREIGN KEY constraint failed message, you may need to enable foreign key constraint checking. Run PRAGMA foreign_keys = ON; to enable foreign key constraint checks.
Delete Associated Records
So let's delete the associated records in the Albums table, before trying the above delete statement again.
DELETE FROM Albums
WHERE ArtistId = 17;
Check that they were deleted:
sqlite> SELECT * FROM Albums WHERE ArtistId = 17;sqlite>
(No results).
Now let's try the original delete statement again:
DELETE FROM Artists
WHERE ArtistId = 17;
Looks promising — no error:
sqlite> DELETE FROM Artists...> WHERE ArtistId = 17;sqlite>
And a SELECT statement confirms the deletion:
sqlite> SELECT * FROM Artists WHERE ArtistId = 17;sqlite>
No results.
Delete all Rows
You can delete all rows in a table by omitting the WHERE clause.
We have a spare table (Albums1), so let's try it on that one.
First, let's just check that it actually contains data:
sqlite> SELECT * FROM Albums1;AlbumId AlbumName Year ArtistId-------- -------------------- ------------ ------------1 Killers 1981 72 Powerslave 1984 73 Surfing with the Ali 1987 14 Heavy as a Really He 1995 115 Yummy Yummy 1994 176 Out of the Loop 2007 67 Suck on This 1989 138 Pork Soda 1993 139 Sailing the Seas of 1991 1310 Flying in a Blue Dre 1989 111 Black Swans and Worm 2010 112 Somewhere in Time 1986 713 Big Red Car 1995 17
It does. So let's go ahead and delete it all:
DELETE FROM Albums1;
Now let's check its contents again:
sqlite> SELECT * FROM Albums1;sqlite>
(No results).
