I had a coworker who would always do manual dangerous SQL like these within a transaction ... and would always mentally compare the "rows affected" with what he thought it should be before committing.
And then commit it.
It's a good habit.
And then commit it.
It's a good habit.
1) Write a select statement capturing the rows you want to modify and verify them by eyeball
2) (Optional) Modify that statement to select the unchanged rows into a temp table to be deleted in a few days
3) Wrap the statement from step 1 in a transaction
4) Modify the statement into the update or delete
5) Check that rowcounts haven't changed from step 1
6) Copy-and-paste the final statement into your ticketing or dev tracking system
7) Run the final statement
I do what your coworker did and it's a great feeling when you get the "451789356 rows updated" message inside a transaction where you are trying to change Stacy's last name after her wedding and all you have to do is run a ROLLBACK.
Then it's time to go get a coffee and thank your deity of choice.
- With shells, I prefix risky commands on production machines with #, especially when cutting and pasting
- Same for committing stuff into VCS, especially when I'm cherrypicking diffs to commit
- Before running find with -delete, run with -print first. Many other utilities have dry-run modes
Then open a transaction around the update with that same where clause, check the total number of rows updated matches the earlier select, then commit.
This approach definitely reduces your level of anxiety when working on a critical database.
UPDATE ImportantTable SET
ImportantColumn = ImportantColumn
WHERE Condition = True
UPDATE ImportantTable SET
ImportantColumn = NewValue
WHERE Condition = True