The Forgotten WHERE

What happens when an UPDATE has no WHERE? It changes every row of the table. This statement was supposed to move one customer to London:

UPDATE customers
SET city = 'London';

Results says Rows changed: 14: all the customers of the shop now live in London, and their real cities are gone:

The database doesn't ask whether you're sure, and there's no Undo button. The same goes for DELETE, which you'll learn in the next exercise: without WHERE, it deletes every row of the table. It's one of the best-known mistakes in programming, and it happens to experienced programmers too, usually when they run a statement before they've finished writing it. Here, the next Run brings the data back. In a real database, the only way back is a backup, if there is one.

How to protect yourself:

• Write the WHERE first and try it out with a SELECT. Before you change rows, look at them: a query with the same WHERE shows exactly the rows that the change will touch. When it finds the right ones, turn SELECT … FROM customers into UPDATE customers SET … and keep the WHERE as it is.
• Check Rows changed. When you meant to change one customer, it should say 1. 14 means that something is wrong.
• Use a transaction, which can undo changes. You'll learn about transactions at the end of this lesson.

For example, Leo Walker has a new email. First the query:

SELECT id, name, email
FROM customers
WHERE id = 8;

It finds one row, the right one, so its WHERE can go into the UPDATE unchanged:

UPDATE customers
SET email = 'leo@walker.uk'
WHERE id = 8;

A WHERE can also find other rows than you meant: more of them (AND goes before OR, and LIKE '%an%' finds more names than you'd expect), or none (= NULL never finds anything). A SELECT with the same WHERE shows you all that before any harm is done.