Making bulk data changes using server-side procedures with transactions
In some cases, you'll need to make bulk changes to your data. In many cases, you need to scroll through the data making changes according to a complex set of rules. You have a few choices in that case:
- Write a single SQL statement that can do everything.
- Open a cursor and read the rows out, and then make changes with a client-side program.
- Write a procedure that uses a cursor to read the rows and make changes using server-side SQL.
Writing a single SQL statement that does everything is sometimes possible, but if you need to do more than just use UPDATE, then it becomes difficult very quickly. The main difficulty is that the SQL statement isn't restartable, so if you need to interrupt it, you will lose all of your work.
Reading all the rows back to a client-side program can be very slow – if you need to write this kind of program, it is better to do it all...