You already know how to insert data into tables. However, in real life we don't just collect records, sometimes we want to get rid of them. Let's find out how we can delete rows from a table with SQL queries.
DELETE statement
To delete all the data from the table but not the table itself, use a query with a DELETE FROM statement.
For example, let's delete all the information from the following table titled books:
title | author | quantity |
|---|---|---|
Thinking in Java | Bruce Eckel | 10 |
Wuthering Heights | Emily Bronte | 20 |
Mansfield Park | Jane Austen | 30 |
The Call of Cthulhu | Howard Lovecraft | 25 |
We can do that with a very simple query:
DELETE FROM books; As a result of the query execution we still have the table books, but it is empty now.
Delete selected rows
You can also use DELETE FROM statement to delete only selected rows from a table.
Let's assume that we have added a few new rows in our table books:
title | author | quantity |
|---|---|---|
Fahrenheit 451 | Ray Bradbury | 0 |
The Gambler | Fyodor Dostoevsky | 0 |
Dandelion Wine | Ray Bradbury | 14 |
As you can see, some books from the table are unavailable (their quantity equals zero). We can delete records about these books with an SQL query with a WHERE clause:
DELETE FROM books
WHERE quantity = 0;This query will delete all the rows from the table books where the logical expression
quantity = 0is true.
After we execute the query, our table books will look like this:
title | author | quantity |
|---|---|---|
Dandelion Wine | Ray Bradbury | 14 |
Conclusion
The following query can be used to delete all the rows without deleting the table:
DELETE FROM table_name; If you want to delete only selected rows, you can use this template:
DELETE FROM table_name
WHERE logical_expression;