In this post, you’ll learn how to delete a row in SQL and how to delete all rows in a table.
Let’s say we have a table called “product” that looks like this:
We want to delete a row from the table – the row with the product_name of “Couch”.
Delete a Row in SQL
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.
You’ll get an output like this:
1 row(s) deleted.
Here’s what the table will look like after the row is deleted:
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:
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.