How does DELETE remove rows, and what happens if you leave off the WHERE clause?
A delete finds the rows that match the condition and removes them one by one.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- Remove one order.
DELETE FROM orders WHERE id = 42;
So what happens if you leave off the condition? Every row in the table is removed. The table itself stays, with its columns and its rules, but it is empty.
And this is the one mistake that ends careers, so it is worth knowing why it is so bad. The statement succeeds. There is no error and no warning. And because a delete is a change to the data, you can only undo it if you put it inside a transaction and roll it back, or if you have a backup. If it runs on its own, the rows are gone.
There are three habits that stop it.
- Look at the rows first. Run the select with the same condition, and if the number is wrong, you have caught it before you did the damage.
- Put the delete inside a transaction, so that you can roll it back while you check.
- Always write the condition. Make it a rule that a delete statement without a condition never goes anywhere near a real database.
There is also the option of the TRUNCATE statement, which empties a table in one go and is much faster on a big table. It cannot take a condition, so it is the tool for "empty this table" and not for "remove these rows".
The next question in this topic asks for a delete that is careful.