SQL: Rollback and Commit – undo mistakes in SQL

On By Ben Larson Ph.D.In MS SQL Server, sql

If you ever want to experience a heart attack, may I advise accidently deleting a production table from a database. And even if you survive the heart attack, your job may not.

This is, IMHO, this single most important piece of SQL code you will ever learn. It is called Rollback and Commit.

What Rollback does, is reverse and changes you have made to the database, allowing you to undelete the table you accidentally sent to the data afterworld.

Here is how it works. You start by letting the system know to you are making changes that you may want to reverse. The syntax is the as follows

Start transaction;

delete from <table> where <column> = <Value>;

Now comes the life saving part. If you realize you made a mistake, simply type Rollback;

If, on the other hand, you like the results, then type Commit;

The results will be saved and the transaction instance will be closed out.

Now lets try adding a new row, using start transaction
If that is not what you want, just type Rollback; and your mistake is gone
On the other hand, if you want to save the results, simply type commit; and the new record will stay

Important NOTE: Rollback only works if you first Start Transaction: — it is two words that will save your job. Start every instance of adding or deleting data or object with Start Transaction: – trust me on this one.

Leave a Reply