SQL ET POSTGRE 4.19 : Deleting data from a table

In this video we see how to delete data from a table. The SQL statement starts with DELETE FROM, followed by the table name and a WHERE clause naming the column and the value used as a filter. We are going to apply this straight away. Let's say we want to remove Vanessa Dupont from our table: we write DELETE FROM exemple WHERE exemple_id = 1;. We run it, it returns successfully, then check with a SELECT * FROM exemple — Vanessa is gone.

Deleting several rows at once

We can also delete several rows in one go, provided they share a common value — the primary key cannot be used here, since it is unique per row. Mike and John are both CA, so we can run DELETE FROM exemple WHERE nationalite = 'CA';. After re-selecting the table, both rows are gone. 
DELETE FROM exemple WHERE exemple_id = 1;
DELETE FROM exemple WHERE nationalite = 'CA';
DELETE FROM exemple;   -- wipes the entire table
  • To delete one row, filter by the primary key.
  • To delete several rows, filter by a shared column value.
  • To wipe the whole table, omit the WHERE clause — use with extreme care.
Finally, we can wipe the entire table in a single statement by running DELETE FROM exemple; with no filter at all. After running it, the table is empty. To summarise: DELETE FROM <table> WHERE <column> = <value> for a targeted deletion, DELETE FROM <table> with no WHERE when you really want to empty the table. In the next video we will apply everything we have learned by inserting and managing data inside our film database.

Summary

This lesson covers SQL DELETE operations for removing data from PostgreSQL tables. It demonstrates three deletion scenarios: removing a single row using a specific condition (WHERE clause with primary key), deleting multiple rows by matching a common column value, and emptying an entire table. The DELETE statement syntax is DELETE FROM table_name WHERE condition, and SELECT queries are used to verify successful deletion.

Key points

  • DELETE query syntax: DELETE FROM table_name WHERE condition specifies which rows to remove
  • Single row deletion: use WHERE clause with primary key (id = 1) to remove specific records
  • Multiple row deletion: identify common values in a column (nationality = 'CA') to delete several rows matching that criterion
  • Delete all data: use DELETE FROM table_name without a WHERE clause to empty the entire table
  • Verification: use SELECT * FROM table_name after deletion to confirm rows have been removed

FAQ

What is the basic syntax for deleting data from a PostgreSQL table?

The DELETE statement syntax is: DELETE FROM table_name WHERE condition. You specify the table name and the WHERE clause defines which rows to delete. Omitting the WHERE clause deletes all rows.

How do you delete multiple rows that share a common value?

Identify a column with a common value across the rows you want to delete, then use WHERE to match that value. For example, DELETE FROM example WHERE nationality = 'CA' removes all rows where the nationality column equals 'CA'.

How can you verify that rows have been successfully deleted?

Execute a SELECT * FROM table_name query after the DELETE operation. If the rows no longer appear in the results, the deletion was successful.