As someone who regularly dealt with updating production database for customers for our software upgrades, I tell you that the best way to minimize errors is to make updates as straightforward as possible.
If you can perform a change to all records rather than specific records, it is preferable.
In other words, if you're given a list of ids of records which need their state changed, you should be asking yourself why the update is being done in the context of the program. It may be that of the 10 records you need to update, the table only has 10 elements. Therefore you should be asking yourself if conceptually all you're doing is updating the state of all records.
If you can insert, it is preferable.
The act of adding a record is self-contained. By this I mean there is only one side effect of adding a record, and that is the existence of a record that didn't exist prior. Therefore unless you're adding a record which shouldn't be there, there should be no issues.
If you can avoid deletion, it is preferable.
If you're performing a deletion, you're removing data which would otherwise be unrecoverable without a backup. If possible, try to organize the data in such a way that you can disable records by changing its state rather than physically deleting the record. The excess of data can be put in a partition or it can be removed entirely in a later moment once you're sure there are no problems.
Have a consistent update policy.
If you need to update a record, one of several things can happen:
- Your record doesn't exist.
- Your record exists but it has already been changed.
- Your record exists and requires the change.
You need to have a policy to determine the course of action should something not go as planned. For simplicity sake, you should be consistent across the board and apply this policy in any situation of this type, not just for specific tables. This makes it easier to be able to recover data later. Generally, my policy is to write the script in such a way as to be able to re-execute it later. Should the script fail, it is nice to know you can make the proper adjustments and re-execute, however you're free to pick your own policy that suits you best.
This by no means excuses you from performing a backup prior to performing any update in a production environment! Though even with a backup, I consider it a failure to have to use the backup. Losing data cannot be a possibility even in the worst-case scenario.
You're not always going to be able to have it your way. The table schema is not likely going to be determined by you, and as such it means the types of updates you can expect to perform will be both complicated and risky. Though if you have any say-so in the matter, it helps to keep these points in mind as they make any updates straightforward and without significant risk.