Delete data stored in tables with SQL. SQL has a special query to delete data stores in tables.

Most of the times records are deleted by id. The id is a unique identifier for every record. Usually starting from 1 upwards. This same id can be used to update data.

You may like:
Learning SQL: Master SQL Fundamentals

Example

Syntax

To delete data you can use the DELETE Query:

1
DELETE FROM table WHERE ...

Delete query

We start with a usual table:

1
2
3
4
5
6
7
8
9
sql> SELECT * FROM Students;
+----------+--------+-----------+-----------+-----------+
| PersonID | Gender | LastName | FirstName | StudentID |
+----------+--------+-----------+-----------+-----------+
| 13148 | 0 | Johnson | Dave | 6391931 |
| 13150 | 1 | Dylan | Abby | 439225 |
| 13151 | 1 | Jobs | Steve | 439225 |
| 13152 | 1 | Bonaparte | Napoleon | 439225 |
+----------+--------+-----------+-----------+-----------+

Decide which record to delete. In this example we use the PersonID to delete the record.

1
2
sql> DELETE FROM Students WHERE PersonID=13152;
Query OK, 1 row affected (0.06 sec)

For this to work, every ID should be unique. If an ID is not unique, you can’t delete using the ID.

If you’d show the table data again, this would show:

1
2
3
4
5
6
7
8
sql> SELECT * FROM Students;
+----------+--------+-----------+-----------+-----------+
| PersonID | Gender | LastName | FirstName | StudentID |
+----------+--------+-----------+-----------+-----------+
| 13148 | 0 | Johnson | Dave | 6391931 |
| 13150 | 1 | Dylan | Abby | 439225 |
| 13151 | 1 | Jobs | Steve | 439225 |
+----------+--------+-----------+-----------+-----------+

Video

Video tutorial on the DELETE query