This trick is certainly nothing new but it's immensely helpful in many cases. Have you ever forgot to highlight a WHERE clause when you were making an Update and accidentally (or almost accidentally) updated a whole bunch of data that you didn't intend to? Well I have, more times than I can count.
A very easy way to safeguard against doing this when you're in a hurry is to wrap the statement in a transaction.
Typically, you'll make a database update with a simple UPDATE statement like this:
| UPDATE PivotStuff SET Week = 45 But let's say that you have a little more complex logic and want to safeguard against accidental unintended consequences. The first thing you can do is create a select statement and use the same predicate in the SELECT and UPDATE statements. However b/c of type conversion, there are times where an UPDATE will still behave differently than the SELECT statement will. Fortunately, all you need to do is add a BEGIN TRANSACTION statement before the update, than run your verification to make sure nothing unintended happened. If it did, just call ROLLBACK, otherwise COMMIT. Here's all there is to it! BEGIN TRANSACTION UPDATE PivotStuff SET Week = 45 SELECT MAX(WEEK) FROM PivotStuff ROLLBACK --If something went wrong, fire this COMMIT --If it all worked well, fire this |