1. Find the data that should be changed.
2. Create the update statement and validate it
3. Run the complete statement.

Find the data

To find the data I run a simple SQL query. In this example I have to update the production country of all Volvo’s in my cars table that have a registration number greater than VLV102.

SELECT *
FROM Cars
WHERE RegistrationNumber > 'VLV102'

Create the Update Statement

Now I now I how to find the correct cars. Time to extend it with an update statement that can be tested without actually changing the database. I do this by wrapping the statements in a transaction which is rolled back.

BEGIN TRANSACTION
 
UPDATE Cars
SET MadeIn = 'Belgium'
WHERE RegistrationNumber >'VLV102'
 
SELECT *
FROM Cars
WHERE RegistrationNumber > 'VLV102'
 
ROLLBACK

Running this will make the update within a transaction. Then the results are selected for display. Finally the transaction is rolled back to undo the changes in the database. This step can be run any number of times when construction a complex update statement without anything being saved in the database.

Run the Complete Statement

Once I’m sure that the script is correct I replace ROLLBACK with COMMIT to have the changes written to the database.

BEGIN TRANSACTION
 
UPDATE Cars
SET MadeIn = 'Belgium'
WHERE RegistrationNumber >'VLV102'
 
SELECT *
FROM Cars
WHERE RegistrationNumber > 'VLV102'
 
-- ROLLBACK
COMMIT

Conclusion

Using transactions when doing manual updates to important databases adds an option to try the script before running it live. I recently showed a colleague of mine this method and it saved him from doing a disastrous update. In his first attempt for the update, he had made a mistake in the where clause, making the update to far too many lines in the table. When the updated line count showed 3500 instead of the expected 12 we where saved by the transaction being rolled back. The failing where clause was fixed and tested once again before we changed the script to committing the transaction.

• 

  robert on 2018-04-29 Reply

  You should consider than a rollback on a live database, with too many lines , can just put your server on his knees , and lock every ressources…

• Leave a Reply

  Your name as it will be displayed on the posted comment.

  Your e-mail address will not be published. It is only used if I want to get in touch during comment moderation.

  Your name will be a link to this address.

  Submit Cancel
• You're currently writing a reply to an existing comment, so the comment form is busy elsewhere. To make a new comment (that isn't a reply to an existing ocmment), you have to cancel that reply.
  Cancel the reply and give me the comment form back!