This shows you the differences between two versions of the page.
Previous revision | |||
— | reference:running_database_updates_within_a_transaction [2018/11/14 10:45] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ======Running database updates within a transaction====== | ||
+ | |||
+ | This is a trick which database experts use, because they have learned (often the hard way) that a simple mistype can trash a database... | ||
+ | |||
+ | Before running any database query which can alter the database (UPDATE, DELETE, INSERT, DROP, CREATE, ALTER) begin a database transaction using the '' | ||
+ | |||
+ | Then, run your update query. | ||
+ | |||
+ | Then, check the results. | ||
+ | |||
+ | Then, either use the '' | ||
+ | |||
+ | For instance, if you are wanting to delete a widget from the database you could just use the command '' | ||
+ | |||
+ | So, to play safe, you would issue the commands: | ||
+ | |||
+ | BEGIN; | ||
+ | DELETE FROM widgets WHERE id=27; | ||
+ | |||
+ | Now, you can check the results of the query. The PostgreSQL **psql** program will display the number of rows deleted from the database. So if the response says '' | ||
+ | |||
+ | After doing the update query, you can also issue SELECT queries or other update queries within the same transaction to check database data or perform more updates. (Note that if you have an error, you will have to ROLLBACK and start again). | ||
+ | |||
+ | Certain other database actions may be queued while you are in the transaction, | ||
+ | |||