Deleting Data

DELETE, and the Sentence That Empties a Table

DELETE removes whole rows. There is no SET list, because there is nothing to change — a row either survives the statement or it stops existing. That makes DELETE the shortest destructive statement in SQL, and the shortness is part of the danger.

Exactly as with UPDATE, the WHERE clause is optional to the parser and compulsory in practice. DELETE FROM students; empties the entire table. It reports success, tells you how many thousands of rows it removed, and offers nothing to undo it with. The table itself survives — the columns, the types, the constraints and the indexes are all still in place — but there is nothing left inside it.

So carry the habit from the previous lesson across unchanged. Write the WHERE clause first, run it as a SELECT, read the rows that come back, and only then swap the word SELECT for DELETE. When you are about to remove data rather than change it, that five-second check is the difference between a routine cleanup and a phone call to whoever holds the backups.

The affected-row count is your second check, and it is worth actually reading rather than scrolling past. MySQL prints something like Query OK, 3 rows affected. If it says 0, your filter matched nothing — usually a typo, a trailing space inside a value, or a comparison written as = NULL instead of IS NULL, rather than "it was already deleted". If it reports a number far larger than you expected, you have just learned something important, and whether that turns out to be a lesson or a disaster depends entirely on whether you were inside a transaction.

-- Step 1: look at exactly which rows would disappear
SELECT id, name, email FROM students WHERE email = 'karan@example.com';

-- Step 2: identical filter, now as a DELETE
DELETE FROM students WHERE email = 'karan@example.com';

-- A set of rows rather than one
DELETE FROM students
WHERE is_active = 0
  AND joined_on < '2021-01-01';

-- Count before you delete in bulk, so you know what to expect
SELECT COUNT(*) FROM students WHERE is_active = 0;

-- MySQL lets you cap how many rows a single statement may remove
DELETE FROM students WHERE is_active = 0 LIMIT 500;

-- DANGER: no WHERE at all. Every student row is removed.
-- DELETE FROM students;

Choosing the Rows With Another Table

Real cleanups are rarely described by a literal value. You want to remove the order items belonging to cancelled orders, or the students who never placed an order at all. The condition lives in a different table, and SQL gives you three ways to say so.

The portable way is a subquery: WHERE id IN (SELECT ...) or, better, WHERE EXISTS (SELECT 1 ...). Every database understands both. MySQL additionally supports a multi-table delete that joins directly in the statement, and PostgreSQL offers DELETE ... USING, which expresses the same idea with different keywords. Use whichever your database provides, but learn to recognise all three, because you will read code that uses each.

There is a trap here that costs people entire afternoons, and it is the NULL rule wearing a different hat. WHERE id NOT IN (SELECT student_id FROM orders) returns no rows at all if even one order has NULL in student_id. NOT IN is really a chain of <> comparisons joined by AND, and any comparison against NULL is unknown rather than false, so the whole condition can never come out true. Nothing errors. The delete simply affects zero rows and you conclude there was nothing to clean up. NOT EXISTS does not behave this way and is the safer default.

MySQL adds a restriction of its own. You cannot delete from a table while a subquery in the same statement selects from that same table in its FROM clause; the error reads "You can't specify target table ... for update in FROM clause". The workaround is the one from the UPDATE lesson: wrap the subquery in one more SELECT, which forces MySQL to materialise it as a temporary table before the delete begins.

-- Portable and safe: NOT EXISTS
DELETE FROM students
WHERE joined_on < '2021-01-01'
  AND NOT EXISTS (
      SELECT 1 FROM orders o WHERE o.student_id = students.id
  );

-- The NOT IN version looks equivalent and is not:
-- one order row with student_id IS NULL makes it delete nothing
-- DELETE FROM students
-- WHERE id NOT IN (SELECT student_id FROM orders);

-- MySQL multi-table delete: drop the items of cancelled orders.
-- The alias after DELETE names WHICH table loses rows.
DELETE oi
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'cancelled';

-- PostgreSQL spells the same thing with USING
-- DELETE FROM order_items oi
-- USING orders o
-- WHERE o.id = oi.order_id AND o.status = 'cancelled';

-- MySQL error 1093 workaround: one more SELECT layer
DELETE FROM students
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM students WHERE marks IS NULL
    ) AS tmp
);

DELETE, TRUNCATE and DROP Are Three Different Things

This comparison comes up in almost every SQL interview, and the reason is that the three statements look related and behave nothing alike.

DELETE removes rows one at a time. It can be filtered with WHERE, it records each removal in the transaction log so the work can be rolled back, and it fires any row-level delete triggers on the table. All of that per-row bookkeeping is why deleting a million rows takes real time.

TRUNCATE TABLE discards every row at once by throwing away the table's storage and starting it again. It accepts no WHERE clause, does not fire row triggers, and is dramatically faster on a large table. In MySQL it also resets AUTO_INCREMENT back to 1, so the next row you insert gets id 1 — which matters if anything outside the database still remembers the old ids.

DROP TABLE removes the table itself. Afterwards the name does not exist at all: a SELECT against it is an error, not an empty result. DROP DATABASE does the same to every table at once.

Whether a TRUNCATE can be undone is a genuine dialect difference, and it is worth knowing precisely rather than approximately. In MySQL, TRUNCATE is DDL: it causes an implicit commit, which ends any transaction you were in, and it cannot be rolled back. Oracle behaves the same way. In PostgreSQL and SQL Server, TRUNCATE is transactional, and a ROLLBACK really does bring the rows back. Never assume the behaviour you saw on one engine carries to another.

• DELETE — filterable with WHERE, row by row, fires triggers, can be rolled back, leaves AUTO_INCREMENT where it was
• TRUNCATE — every row, no WHERE, no row triggers, very fast, resets AUTO_INCREMENT in MySQL
• DROP TABLE — structure and data both gone; the table name stops existing
• DROP DATABASE — every table in the database, removed by one statement
• MySQL with InnoDB refuses to TRUNCATE a table that another table's foreign key points at; DELETE still works there

-- Empty the table but keep its shape
TRUNCATE TABLE enquiries;

-- Same end result, but row by row, and rollback-able
DELETE FROM enquiries;

-- Remove the table itself
DROP TABLE enquiries;

-- The IF EXISTS forms do not error when the object is already gone,
-- which is what makes a setup script safe to run twice
DROP TABLE IF EXISTS enquiries;
DROP DATABASE IF EXISTS practice_db;

-- After TRUNCATE in MySQL, ids start again from 1
TRUNCATE TABLE toppers;
INSERT INTO toppers (name, branch, marks) VALUES ('Meera Nair', 'CSE', 91);
SELECT id FROM toppers;      -- 1, not the old maximum plus one

-- DELETE leaves the counter untouched, so the next insert
-- continues from the highest id the table ever had
-- DELETE FROM toppers;

Foreign Keys Decide What a DELETE Is Allowed To Do

The moment two tables are linked by a foreign key, deleting from the parent stops being a private decision. If orders.student_id references students.id, what should happen to a student's orders when the student is deleted? The database refuses to guess. You choose at the time you create the constraint, with an ON DELETE clause.

The default is RESTRICT — written NO ACTION in the standard, and effectively identical in MySQL. It refuses the delete for as long as any child row still points at the parent. MySQL's message is "Cannot delete or update a parent row: a foreign key constraint fails", and beginners read it as the database being awkward. It is the opposite: it has just stopped you creating orders that belong to nobody.

ON DELETE CASCADE removes the children automatically. That is correct when the child rows have no meaning on their own — an order_items row genuinely cannot exist without its orders row. It is dangerous when they do have meaning. Putting CASCADE between students and orders means deleting one student silently erases their entire purchase history, including the money.

ON DELETE SET NULL keeps the child row and blanks the link, which requires the foreign key column to be nullable. It fits a genuinely optional relationship: a product losing its category should still be a product.

The thing to watch with CASCADE is that it is invisible at the point of use. Your statement says "delete one student" and the affected-row count says 1, while three orders and eleven order items also disappear because the cascade chained down two levels. Before you enable it, run the counts yourself so you know the true blast radius of a single-row delete.

-- Children that cannot exist alone: cascade is right here
CREATE TABLE order_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Records you must not lose: keep the default and refuse the delete
CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    ordered_at DATETIME,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE RESTRICT
);

-- An optional link: keep the product, forget the category
ALTER TABLE products
ADD CONSTRAINT fk_products_category
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

-- Know the blast radius before deleting a parent row
SELECT COUNT(*) AS orders_affected
FROM orders WHERE student_id = 3;

SELECT COUNT(*) AS items_affected
FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE student_id = 3);

-- With no cascade, delete children first, then the parent
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM (SELECT id FROM orders WHERE student_id = 3) AS t
);
DELETE FROM orders   WHERE student_id = 3;
DELETE FROM students WHERE id = 3;

Soft Deletes: What Real Applications Usually Do

Production systems delete far less than you would expect. Instead of removing a row they mark it as removed — typically a deleted_at column that holds NULL while the record is live and a timestamp once it is not. This is called a soft delete.

The reasons are practical rather than theoretical. A user who deleted something by mistake can be restored in seconds instead of from last night's backup. An order that was cancelled still has to appear in last month's revenue report. And a foreign key pointing at a row that no longer exists is an entire category of bug that simply cannot happen if nothing is ever really removed.

The cost is that every query now has to remember. One forgotten WHERE deleted_at IS NULL and deleted students reappear in a list — or, more quietly, in a COUNT that somebody reports to a client. The standard defence is a view, or an application framework that adds the condition for you, so that day-to-day queries are not able to forget.

There is a subtler cost that catches teams out months later. A soft-deleted row still physically exists, so it still occupies its slot in every UNIQUE index. Delete the student whose email is ananya@example.com, then let her register again: the insert fails with a duplicate-key error against a row the application believes is gone. PostgreSQL solves this directly with a partial index — CREATE UNIQUE INDEX ... ON students (email) WHERE deleted_at IS NULL — which constrains only the live rows. MySQL has no partial indexes, so the usual workaround is to blank or rename the unique column at the moment of soft deletion. Neither fix is difficult, but both are much easier to put in at design time than to retrofit.

-- The column: NULL means the row is live
ALTER TABLE students ADD COLUMN deleted_at DATETIME NULL DEFAULT NULL;

-- "Deleting" a student
UPDATE students SET deleted_at = NOW() WHERE id = 7;

-- Undoing it, which a real DELETE could never offer
UPDATE students SET deleted_at = NULL WHERE id = 7;

-- Every ordinary query must now carry this condition
SELECT id, name, branch FROM students WHERE deleted_at IS NULL;

-- A view, so that everyday queries cannot forget
CREATE VIEW active_students AS
SELECT id, name, email, branch, marks
FROM students
WHERE deleted_at IS NULL;

SELECT COUNT(*) FROM active_students;

-- Freeing the unique email when a row is soft-deleted (MySQL)
UPDATE students
SET deleted_at = NOW(),
    email = CONCAT('deleted+', id, '@invalid.local')
WHERE id = 7;

-- PostgreSQL can enforce uniqueness over the live rows only
-- CREATE UNIQUE INDEX uq_students_email_live
-- ON students (email) WHERE deleted_at IS NULL;

Removing a Lot of Rows Without Hurting Anything

A single DELETE that removes five million rows is one enormous transaction. Until it finishes, the database is holding locks, writing every removed row into its logs so the whole thing could still be rolled back, and slowing or blocking other work. On a live system, that is exactly how a tidy-up script becomes an outage.

The fix is to delete in batches: a loop that removes a few thousand rows at a time and repeats until nothing is left. Each batch commits on its own, locks are held only briefly, and other queries keep flowing between batches. MySQL makes this easy with DELETE ... LIMIT n. PostgreSQL has no LIMIT on DELETE, so you select the next batch of ids in a subquery instead.

Archive before you delete whenever the rows have any lasting value. INSERT INTO ... SELECT copies them into an archive table, and running the copy and the delete inside one transaction means either both happen or neither does. It costs one extra statement and converts an irreversible action into a reversible one.

Two closing guards, both unglamorous. Take a mysqldump before any bulk delete on data you care about. And run destructive statements inside START TRANSACTION, so that a SELECT can confirm the result before you decide between COMMIT and ROLLBACK. The whole point of both habits is that they cost you nothing on the day everything goes right.

-- Archive first, then remove, both inside one transaction
START TRANSACTION;

INSERT INTO orders_archive (id, student_id, ordered_at, total_amount, status)
SELECT id, student_id, ordered_at, total_amount, status
FROM orders
WHERE ordered_at < '2022-01-01';

DELETE FROM orders WHERE ordered_at < '2022-01-01';

-- Confirm before keeping it
SELECT COUNT(*) AS archived FROM orders_archive;
SELECT COUNT(*) AS still_there
FROM orders WHERE ordered_at < '2022-01-01';   -- expect 0

COMMIT;      -- or ROLLBACK; if either number looks wrong

-- Batched delete in MySQL: repeat until 0 rows are affected
DELETE FROM audit_log
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 5000;

-- PostgreSQL has no DELETE ... LIMIT; choose the batch by id
-- DELETE FROM audit_log
-- WHERE id IN (
--     SELECT id FROM audit_log
--     WHERE created_at < '2024-01-01'
--     ORDER BY id LIMIT 5000
-- );

-- And before any of it, from the command line
-- mysqldump -u root -p practice_db > backup_before_cleanup.sql