How to Delete a Row in SQL

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.