Online College Courses for Credit

+
DELETE FROM to Remove Row

DELETE FROM to Remove Row

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify the correct DELETE FROM statement to delete a single row in a table, given its primary key (ID).

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the DELETE statement to remove rows in a table.

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;

File:11374-1460-1.png

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;

File:11375-1460-2.png

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

File:11376-1460-3.png

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.

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