SQL Delete

Previous Page Next Page

Deleting Data from Tables

Just like inserting records into a table with the INSERT statement, you can also remove records from a table using the DELETE statement.

Syntax

The DELETE statement is used to delete one or more rows from a table.

DELETE FROM table_name WHERE condition;

Warning: The WHERE clause in the DELETE statement determines which record or records should be deleted. While optional, omitting or forgetting the WHERE clause will result in all records being permanently deleted from the table.

Let's delete some records from the persons table that we created in the create table chapter.

Assume our persons table currently contains the following records:

+----+--------------------+------------+-------------+
| id | name               | birth_date | phone       |
+----+--------------------+------------+-------------+
|  1 | Peter Wilson       | 1990-07-15 | 0711-020361 |
|  2 | Carrie Simpson     | 1995-05-01 | 0251-031259 |
|  3 | Victoria Ashworth  | 1996-10-17 | 0695-346721 |
|  4 | George Bailey      | 1993-03-05 | 0897-034214 |
|  5 | Norman Bates       | 1999-08-25 | 0522-556721 |
+----+--------------------+------------+-------------+

Delete Records Based on Conditions

This statement deletes rows from the persons table where the id is greater than 3.

Example

Try this code »

DELETE FROM persons WHERE id > 3;

After executing the query, the persons table will appear like this:

+----+--------------------+------------+-------------+
| id | name               | birth_date | phone       |
+----+--------------------+------------+-------------+
|  1 | Peter Wilson       | 1990-07-15 | 0711-020361 |
|  2 | Carrie Simpson     | 1995-05-01 | 0251-031259 |
|  3 | Victoria Ashworth  | 1996-10-17 | 0695-346721 |
+----+--------------------+------------+-------------+

Note: Refer to the tutorial on SQL WHERE clause to learn how to construct complex queries based on multiple conditions when deleting records from tables.

Delete All Data

Similarly, as mentioned earlier, if you do not specify a WHERE clause in the DELETE statement, all rows from the table will be deleted. However, the table structure, attributes, and indexes will remain intact.

The following statement removes all records from the persons table:

Example

Try this code »

DELETE FROM persons;

Now, if you attempt to select records from the persons table, you'll receive an empty result set.

Previous Page Next Page