Scripts to Update Database Values
Executing scripts to update incorrect values in a database should be done with care. If a script dynamically updates a value based on a calculation it is possible that each subsequent execution of the script will result in different database values.
In the following example all products in category 3001 will be updated with a 20% price increase. If the script is accidentally run more than once the new price will be much greater than the anticipated 20%.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| —this code increases the price of all products | |
| —in the category by 20% each time the script is run | |
| —The result of the script is different each time the script is run. | |
| UPDATE Product | |
| SET Price = Price * 1.2 | |
| WHERE CategoryId = 3001 |
In order to make the script idempotent you should plan it in such a way that the code could be run multiple times but that the final result is always the same. This generally means that your script will require additional planning and verification. Some potential options are…
- Include all new product prices in the script explicitly – this ensures that new product prices will be the same each time the script is run.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| UPDATE Product SET Price = 12.95 WHERE ProductId = 1435 | |
| UPDATE Product SET Price = 23.70 WHERE ProductId = 1436 | |
| UPDATE Product SET Price = 31.95 WHERE ProductId = 1498 | |
| … |
- Create a temporary table of current (or new) product prices , this could be stored/cached in a different database to isolate it from changes in the database being modified. The temp table of prices must be created before the update script runs and should not be deleted until you are certain that the update has succeeded. The script can then be run any number of times and always have the same outcome. (NOTE: this approach has external dependencies and introduces the possibility of change – which makes it possible that it could be non-idempotent – so care should be taken with this approach)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — the following external database with new prices should have ben created before execution of this script | |
| /* | |
| CREATE TABLE tmpProductPrices | |
| ( | |
| ProductId int | |
| , Price number(10,2) | |
| ) | |
| */ | |
| UPDATE p | |
| SET p.Price = tmp.Price | |
| FROM Products AS p | |
| INNER JOIN tmpProductPrices as tmp | |
| ON p.ProductId = tmp.ProductId |
Other Database Scripts
The previous examples deal with executing scripts to update data in the database. Other scripts may include:
- Inserting new data – writen as an UPSERT (or using INSERT or UPDATE logic)
- Modifying table structure (this requires logic to DROP IF EXISTS logic before adding a new column)
- Adding new lookup or joiner tables – This requires a script to populate the new table with consistent data
There are many more possibilities of creating database scripts which should be written in an idempotent manner.
Summary
Every database script that you create should be verified by the DBA to ensure that it is idempotent. This also means that it should not matter in which order the database maintenance scripts are executed.
The key to idempotent database scripts is that the script MUST PRODUCE THE SAME RESULT EVERY TIME IT IS EXECUTED. This must be the primary goal of creating custom database scripts that will be run in a production environment.
Leave a Reply