In this post, you’ll learn how to delete a row in SQL and how to delete all rows in a table.
Sample Data
Let’s say we have a table called “product” that looks like this:
| id | product_name | price | category |
| 1 | Chair | 80 | Dining |
| 2 | Desk | 250 | Office |
| 3 | Couch | 600 | Living |
| 4 | Office Chair | 120 | Office |
| 5 | Coffee Table | 180 | Living |
| 6 | Small Bin | 40 | Bathroom |
We want to delete a row from the table – the row with the product_name of “Couch”.
Delete a Row in SQL
To delete a row in SQL, you use the DELETE keyword. You also use the WHERE keyword to specify the criteria of the row to delete.
In this example, we want to delete the row with the product_name of “Couch”. Our statement looks like this:
DELETE FROM product
WHERE product_name = 'Couch';When we run this statement, it will delete all rows that have a product_name of Couch. In this example it will delete one row.
This query will work in all variations of SQL: Oracle, SQL Server, MySQL, PostgreSQL, and more.
You’ll get an output like this:
1 row(s) deleted.
Here’s what the table will look like after the row is deleted:
| id | product_name | price | category |
| 1 | Chair | 80 | Dining |
| 2 | Desk | 250 | Office |
| 4 | Office Chair | 120 | Office |
| 5 | Coffee Table | 180 | Living |
| 6 | Small Bin | 40 | Bathroom |
Delete Multiple Rows in SQL
What if you want to delete multiple rows? Let’s say we wanted to delete all rows where the category is Office.
Our statement would look similar:
DELETE FROM product
WHERE category = 'Office';The output would look something like this:
2 row(s) deleted.
Our table data now looks like this:
| id | product_name | price | category |
| 1 | Chair | 80 | Dining |
| 5 | Coffee Table | 180 | Living |
| 6 | Small Bin | 40 | Bathroom |
You don’t always have to delete based on a column equalling a text value.
Other DELETE statements we could have run, which will delete different rows, could have included different WHERE clauses:
WHERE price = 80;WHERE price > 100;WHERE product_name LIKE 'C%';So, when you write your WHERE clause, you can use different methods to meet the criteria that you need.
Delete All Rows
If you want to delete all rows from a table, you can write a DELETE statement without a WHERE clause. The WHERE clause is optional, and without it, all rows are deleted.
DELETE FROM product;When you run this query, all rows are deleted.
Another way to delete all rows is to use the TRUNCATE statement. The main difference is that TRUNCATE does not allow for the operation to be rolled back, or “undeleted”, so it is often faster.
Hopefully, this short guide will help you understand how to delete a row in SQL.
If you have any questions or comments, let me know in the comments section below.