Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

DELETE FROM to Remove Row

Author: Sophia

what's covered
This tutorial explores using the DELETE statement to remove rows in a table in two parts:
  1. Using the DELETE FROM Statement
  2. Foreign Keys
  3. Deleting Multiple Rows

1. Using the DELETE FROM Statement

The DELETE statement is a relatively easy statement to learn, as it uses the same WHERE clause that you have seen with the SELECT and UPDATE statements. The syntax for the DELETE statement looks like the following:


DELETE FROM <tablename>
WHERE <condition>;

In the syntax, we first have the name of the table that we want to remove rows from listed after the DELETE FROM keywords. Note that we can only delete from one table at a time. Then we use the WHERE clause to specify which rows from the table we want to delete. Like the UPDATE statement, if we do not use the WHERE clause, the DELETE statement will end up deleting all the rows in our table.


DELETE FROM invoice_line;

SELECT * 
FROM invoice_line;

DELETE FROM Successful Query Message

Oops! Although there are times when we may want this, this is usually not what we want to do with our DELETE statements.

In PostgreSQL, we can also use the RETURNING keyword to return the rows that have been removed from the table. However, it’s a better practice to test your WHERE clause with a SELECT statement before running the DELETE statement.

2. Foreign Keys

It's important to note that we cannot delete rows that are referenced by the foreign key from other tables. For example, if we tried to delete the invoice where the invoice_id is equal to 1:


DELETE FROM invoice
WHERE invoice_id = 1;

DELETE FROM Query Failure Message

We are not able to delete it, as there is a foreign key from the invoice_line table. This is similar to what we saw with the DROP table, and we must delete data in the same order.


DELETE FROM invoice_line
WHERE invoice_id = 1;

DELETE FROM invoice
WHERE invoice_id = 1;

This time, we were successful in deleting the invoice_id = 1 from both tables. This is important to avoid having rows of data that have linking issues.

DELETE FROM Successful Query Message 2

3. Deleting Multiple Rows

We can also use the WHERE clause to delete multiple rows and options at the same time. For example, we could remove all the invoices between 1-10:


DELETE FROM invoice_line
WHERE invoice_id BETWEEN 1 AND 10;

DELETE FROM invoice
WHERE invoice_id BETWEEN 1 AND 10;

We could also base it on a specific range:


DELETE FROM invoice_line
WHERE invoice_id > 50;

DELETE FROM invoice
WHERE invoice_id > 50;


Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

NOTE: It is okay to practice deleting rows in the learning SQL tool. If you need to recover missing data or tables from the original database, just reload the browser page for the tool or come back to this tutorial and launch the database again.

summary
The DELETE statement provides a means to remove rows from tables.

Source: Authored by Vincent Tran