What's your #1 way to be careful with a live database?

If you're using Oracle or another database that supports it, verify your changes before doing a COMMIT.

BEGIN TRANSACTION;

That way you can rollback after a mistake.

NEVER do an update unless you are in a BEGIN TRAN t1--not in a dev database, not in production, not anywhere. NEVER run a COMMIT TRAN t1 outside a comment--always type

--COMMIT TRAN t1

and then select the statement in order to run it. (Obviously, this only applies to GUI query clients.) If you do these things, it will become second nature to do them and you won't lose hardly any time.

I actually have a "update" macro that types this. I always paste this in to set up my updates. You can make a similar one for deletes and inserts.

begin tran t1
update
set
where
rollback tran t1
--commit tran t1

**Make sure your query has a <code>WHERE</code> parameter specified**

I was once mid-way through a complex update, got distracted, and finished the query early, forgetting the "where" clause. Then I got that sinking feeling, watching a half-second query rumble on for 3.. The several hours afterwards spent cleaning up customer data was quite the lesson!

A result of which is now when I work on the live db, I structure my queries like:

UPDATE my_table WHERE condition = true;

then go back and put in the columns etc to update. Takes a bit longer to write, but *massively* reduces my chance of making the same mistake again!

1. Check, recheck, and check again any statment that is doing updates. Even if you think you're just doing a simple, single column update, sooner or later you will not have enough coffee and forget a 'where' clause, nuking a whole table.


A couple other things I've found helpful:

* if using MySQL, enable [Safe updates][1]

* If you have a DBA, ask them to do it.

I 've found these 3 things have kept me from doing any serious harm.


[1]:

[To see links please register here]

Do the exact same update in a Development environment first to make sure it works properly.

Do a backup first: it should be the number 1 law of sysadmining anyways

**EDIT**: incorporating what others have said, make sure your UPDATES have appropriate WHERE clauses.

Ideally, changing a live database should never happen (beyond INSERTs and basic maintenance). Changing the live DB's structure is especially fraught with potential bad karma.

To answer my own question:

When writing an update statement, write it out of order.

1. Write `UPDATE [table-name]`
2. Write `WHERE [conditions]`
3. Go back and write `SET [columns-and-values]`

Choosing the rows you want to update before you say what values you want to change is much safer than doing it in the other order. It makes it impossible for `update person set email = '[email protected]'` to be sitting in your query window, ready to be run by a misplaced keystroke, ready to mess up every row in the table.

Edit: As others have said, write the `WHERE` clause for your deletes before you write `DELETE`.

Often before I do an UPDATE or DELETE, I write the equivalent SELECT.

Turn off AutoCommit in Database IDE if it supports it. I have it turned off in Oracle SQL Developer all the time.