Best practices for updating data base


Before reading this post you may want to get familiar with our post about SQL INSERT Statement.

ROLLBACK is a command that can bring the state of the data a step back, but not more than that.

In a nutshell, these are the SQL rules regarding transaction control.

We must be very careful when using COMMIT and ROLLBACK – in other words, when we are changing the state of our data set.

Check what we have in the “Departments duplicate” table – nine rows and two columns with information. For the sake of this exercise, we will change all the department numbers and names in this table. “Departments Duplicate” is a table now comprising identical rows!

Right before that, we will execute a COMMIT command. Now, if we write the code that updates the “Department Duplicate” table, setting a department number equal to D-0-1-1 and a department name “Quality Control”, and then run it, we will modify all rows of the data table. This would typically happen by mistake – when the WHERE clause and the accompanying conditions have not been added. When executed, it will take us to the last COMMIT that has been run.

Please note that it will be very hard to practice these statements unless we switch off the following feature of Workbench.