DELETE FROM to Remove Row

The DELETE statement is quite an easy statement to learn as it uses the same WHERE clause as 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 that 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;

Oops! Although there are times that we may want this, this most likely is 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.

It is 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;

We are not able to delete this as there is a foreign key from the invoice_line table. This works very similarly to the issues we had previously 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 do not have any linking to

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 line:

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;

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.