14. DELETE: removing rows

DELETE removes rows. Like UPDATE, the WHERE clause decides which ones — and forgetting it wipes the whole table.

Seeded table for this page:

CREATE TABLE product (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR NOT NULL,
  price    DECIMAL(6,2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT true
);
INSERT INTO product (id, name, price, in_stock) VALUES
  (1, 'Notebook', 4.50, true),
  (2, 'Pen',      1.25, true),
  (3, 'Backpack', 39.00, false),
  (4, 'Mug',      8.00, true);

Delete specific rows

DELETE FROM product
WHERE in_stock = false;

SELECT * FROM product ORDER BY id;

Only the Backpack (the one out-of-stock row) is gone.

No WHERE deletes everything

DELETE FROM product;    -- empties the entire table

SELECT * FROM product;

Same warning as UPDATE: without WHERE, every row goes. When you genuinely want to empty a big table, TRUNCATE product; does it faster than DELETE, because it skips removing rows one at a time.

A safe habit: SELECT first

Before a real DELETE, run the same WHERE as a SELECT to see exactly what will disappear:

SELECT * FROM product WHERE price < 2.00;   -- preview
-- happy with the rows? swap SELECT * for DELETE

Try it

Delete every product priced under 5.00, then select what remains, ordered by id.

-- delete the cheap rows, then select what's left ordered by id

DELETE FROM product WHERE price < 5.00;
SELECT * FROM product ORDER BY id;