Lesson 8 of 15 · 18 min
Removing rows with DELETE
By the end of this lesson you can write a DELETE that removes only the intended rows, preview it first, explain why a foreign key blocks a delete, and delete rows based on another table.
DELETE FROM table WHERE condition removes every row for which the condition is true. Like UPDATE, it tests each row against the WHERE; unlike UPDATE, there is nothing to set: the matching rows disappear. PostgreSQL then reports the number of rows removed, for example DELETE 1.
And like UPDATE, DELETE FROM table without a WHERE removes every row. The table itself remains (empty, with all its columns and rules), but the data is gone. Outside a transaction there is no undo; in real systems the only way back is a backup, which may be hours old.
That is why the safe workflow matters even more for DELETE:
- Write the WHERE as a SELECT first and look at the rows (or at least
count(*)). - Turn the SELECT into a DELETE by replacing
SELECT … FROMwithDELETE FROM, keeping the WHERE untouched. - Compare the reported row count with the preview.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons