SQL DELETE

Forget a row only when it should not exist. A missing WHERE deletes the table's contents; a status flag keeps history.

Preview DELETE with the same WHERE

Physical delete forgets. Before you run it, select the same predicate. If the preview is more than the inactive sticker, stop. The write would have been that population too.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 0),
  (3, 'Schema poster', 1);

SELECT product_id, product_name, is_active
FROM products
WHERE product_id = 2 AND is_active = 0;

Edit the query, predict the rows it will return, then run it.

DELETE ... WHERE id = ...

Use DELETE FROM products WHERE product_id = 2 AND is_active = 0 when the row should not exist—duplicate imports, true mistakes, data you are not allowed to keep. Combine identifiers and status so a published product cannot vanish because of a copied product_id alone. Preview first, then delete, then select what remains.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 0),
  (3, 'Schema poster', 1);

DELETE FROM products
WHERE product_id = 2 AND is_active = 0;

SELECT product_id, product_name, is_active
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

A status change keeps history

If orders, reports, or support tickets still refer to a product, deleting the row creates orphans or broken history. Setting is_active = 0 hides it from the live catalog while leaving the fact in place. That is a soft delete. Choose it when the record still has meaning. It is an UPDATE, not a DELETE, and it lives here so you can choose the right verb.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 1),
  (3, 'Schema poster', 1);

UPDATE products
SET is_active = 0
WHERE product_id = 2;

SELECT product_id, product_name, is_active
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

TRUNCATE and DROP are different operations

This lesson's tool is a targeted DELETE ... WHERE. TRUNCATE empties a table in one step on engines that support it. DROP TABLE removes the table itself. Neither is a one-row cleanup, and neither is the habit this page is teaching.

Independent lab: delete one inactive product

Preview product 2 with product_id and is_active, delete that inactive row, then select the catalog. Products 1 and 3 must remain. If you delete by product_id alone, you have skipped the status rule the lab is practicing.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 0),
  (3, 'Schema poster', 1);

-- Preview first:
SELECT product_id, product_name, is_active
FROM products
WHERE product_id = 2 AND is_active = 0;

DELETE FROM products
WHERE product_id = 2 AND is_active = 0;

SELECT product_id, product_name, is_active
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Deleting without a preview SELECT that uses the same WHERE.
• Writing DELETE FROM products; and emptying the table.
• Deleting a row that other tables or reports still need to explain — prefer a status change.
• Targeting only an id when the business rule also depends on status.
• Trusting the delete succeeded without reading the remaining rows.

Lesson review

DELETE removes matching rows. Preview with the same WHERE, target id and any status that is part of the rule, then read what remains. A missing WHERE deletes the table's contents. A status flag keeps history when the fact still matters. TRUNCATE and DROP are different operations.

• I can preview a DELETE with the same WHERE.
• I can write DELETE ... WHERE id = ... for a reviewed row.
• I can choose physical delete versus a status change.
• I can verify neighboring rows are still there after the write.