What actually goes wrong when an UPDATE or DELETE runs without a WHERE clause, and how do you guard against it?
The whole table is changed. The statement succeeds, there is no error and no warning, and the rows are gone.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
status VARCHAR(20) NOT NULL
);
-- This sets the status of EVERY row in the table.
UPDATE tickets SET status = 'closed';
So why is it so bad? Three reasons.
The first one is that it looks right. The statement is the same shape as the one you meant to write, and there is nothing in it that says anything is wrong.
The second one is that it succeeds quietly. The database has no idea that you meant one row. It has a condition that matches everything, so it does exactly what you asked.
The third one is that it is hard to undo. A delete can only be undone if it was inside a transaction that you rolled back, or if you have a backup. An update on a table with real data can take the whole working day to fix by hand.
So how do you guard against it? The habit that helps most is to always write the condition with a key in it. The condition WHERE id = 42 changes one row by construction, and there is no way for it to reach anything else.
-- Safe. One row, and only one row.
UPDATE tickets SET status = 'closed' WHERE id = 42;
-- Risky. However many rows match.
UPDATE tickets SET status = 'closed' WHERE status = 'open';
And then there are the two habits that catch the rest. Look at the rows first with a select that has the same condition, and put the change inside a transaction so that you can roll it back while you check.
The next two questions in this topic look at the safety tools and at the whole pattern together.