One of the developers I used work with got into a habit of always writing the WHERE
clause first and only then going back and writing the UPDATE
or DELETE
part of the query.
It's also a good habit to always write a SELECT
query and verify that it matches the rows you want before changing the SELECT
part to UPDATE
.
Alternatively, you can use the --i-am-a-dummy
setting on your MySQL client. This stops you (and everyone else) from doing UPDATE
or DELETE
queries without a WHERE
clause. It also has some other effects that can prevent long running queries and large result sets.
None of this helps if you are running DROP
queries. For that, you are better off creating a separate user and making sure your normal user doesn't have the DROP
privilege. You will most likely get a permission denied error and have to log out and then log back in as the more privileged user before trying the query again, giving you plenty of time to re-think the query.