What is ROLLBACK in SQL?

ROLLBACK leads the current transaction to traverse back, i.e., it reverts all the updates performed by the SQL statements and restores the database to the way it was before the first statement of the transaction process. ROLL BACK IN SQL

These are a few factors on which ROLLBACK is usually decided on:

Duration for which the transaction has been running
Already updated data by transaction
Data yet to be updated by the transaction process.

Note: Data changes captured in the log files get discarded and are not reflected in the database. Old values can be restored by ROLLBACK using the log entries. That is why this process is also known as "Undoing".

Syntax:

How to Rollback Deleted Data in SQL?

Let's see now how to restore deleted data using SQL.

Say we have a table chocolate_storedb

Prod_ID	brand	chocolate	sales/day
055	Nestle	Milky Bar	10000000
056	Amul	Dark Chocolate	500000
057	Gandour	Safari	200000
058	Cadbury	Dairy Milk	1000000
059	Nestle	Kitkat	500000

Let's delete all the contents from this table.

All contents are now deleted from the table.

Let us now use ROLLBACK and then select command to see the table.

Output:

Prod_ID	brand	chocolate	sales/day
055	Nestle	Milky Bar	10000000
056	Amul	Dark Chocolate	500000
057	Gandour	Safari	200000
058	Cadbury	Dairy Milk	1000000
059	Nestle	Kitkat	500000

Hence, we see that the data is restored after using the ROLLBACK command in SQL.

How to Rollback Updated Query in SQL?

Let us take the same table, chocolate_storedb, and update the sales value.

Prod_ID	brand	chocolate	sales/day
055	Nestle	Milky Bar	100000
056	Amul	Dark Chocolate	5000
057	Gandour	Safari	2000
058	Cadbury	Dairy Milk	10000
059	Nestle	Kitkat	5000

We see the output here as an updated table.

Let's now use ROLLBACK to restore the table.

Output:

Prod_ID	brand	chocolate	sales/day
055	Nestle	Milky Bar	10000000
056	Amul	Dark Chocolate	500000
057	Gandour	Safari	200000
058	Cadbury	Dairy Milk	1000000
059	Nestle	Kitkat	500000

Hence, here we see that the ROLLBACK command restored the table after getting updated.

Example of ROLLBACK in SQL

Let's say we create the following students_table.

student_id	student_name	age	marks_scored
207	Sylvia Plath	22	94
208	John Keats	21	82
209	Rudyard Kipling	22	87
210	W.B Yeats	21	76
211	T.S Eliot	20	98

This deletes the row, and the table appears as:

student_id	student_name	age	marks_scored
207	Sylvia Plath	22	94
208	John Keats	21	82
209	Rudyard Kipling	22	87
211	T.S Eliot	20	98

Now we use ROLLBACK

and on viewing the table again using the select command, we see the table back to its original form.

Final output:

student_id	student_name	age	marks_scored
207	Sylvia Plath	22	94
208	John Keats	21	82
209	Rudyard Kipling	22	87
210	W.B Yeats	21	76
211	T.S Eliot	20	98

We see an undo of the changes made.

Learn More

To learn more about SQL in detail click here.

Conclusion

We learned that ROLLBACK in SQL is a method of undoing the changes made to the database.
The old values are therefore restored, and the database disk changes are discarded. Now that we know how to revert back the changes in a database, it's time to play around with some SQL codes on your editor.