Here’s a free tip. When you’re making ad hoc changes to a database directly from the database client, it is a good idea to stick LIMIT 1 on your UPDATE and DELETE statements if your intent is to only update one row. Even if you want to update many rows, I still recommend a dry run with LIMIT 1 first.
Then if you make an error in your boolean logic or leave something out of the WHERE clause, the wreckage will be limited. It’s much easier to deal with the loss or unwanted alteration of one record than a whole database.
Of course, wrapping your ad hoc queries inside a transaction is also a brilliant idea. I recommend that approach as well assuming it’s available.
October 28, 2008 at 6:43 pm
Or you could ensure you only run your queries from a file to eliminate the possibility of errors while typing in the db client.
And obviously, test that query in a dev/qa environment before unleashing on a production database.
October 28, 2008 at 8:04 pm
Good tip. See point #1 🙂
http://zackola.com/blog/2008/08/07/some-things-you-will-only-learn-by-screwing-up/
October 28, 2008 at 9:56 pm
Uh oh! It sounds like some grumpypants blew away his data inadvertently.
October 29, 2008 at 8:59 pm
SQL’s design is pretty appalling. I mean, for instance, that the default when you don’t supply a WHERE clause is to operate on EVERY SINGLE ROW in a table is just horrifyingly dangerous. “DELETE FROM VeryImportantRecords”, hit Enter by accident, oosp. For all the theoretical elegance of relational databases, the practical fact that most of the time you just want to work on one record has very poor support.
I never do updates or deletes by typing at the shell for that reason. Always edit in a real editor, then cut-and-paste over.
I also do a SLECT COUNT(*) with the WHERE clause I intend to use in an update or delete before doing the actual update or delete. Good (in fact especially important) even if you expect to match only 1 row.
Basically though, it is best to avoid ad-hoc SQL and stick to tested utility programs to do specific things. If you’re going to do it more than once, put it in a script. If you regularly need to make single-field changes to single records, write a program to do that.