Rollback SQL

ROLLBACK is a command in SQL that is used to undo a set of transactions that were last done in the database. The rollback command is used when there is a problem with the database, and then it becomes essential to undo all transformations made to the database since the last successful commit.

When a ROLLBACK command runs, it reverts the current transaction made to the database. After that, the database returns to the state it was in before the start of the transaction.

The syntax of the ROLLBACK command is written as:

It is important to know that the ROLLBACK command reverts all the last transactions used in the database. If you do not want to roll back all transactions, you can use the COMMIT command, which can make successful transactions permanent. Rollback moves in the opposite direction, so it reverses the previous transaction first, then the others. If you want to roll back the last two transactions, then you have to use the COMMIT command before the last two transactions. By doing this, it will make all the transactions permanent except the last two transactions. We will discuss COMMIT and ROLLBACK commands further in examples.

Let us understand the ROLLBACK command properly with the following examples:

Example 1:

Let us consider that we have created a table named employee_table, which contains the fields of emp_id, emp_name, emp_gender, emp_age, emp_city, and emp_salary.

We will use the following command to delete some data from employee_table,

Output:

As you can see, the rows with salaries less than 15,000 have been deleted.

Now, we will use the ROLLBACK command to revert the last command used in the database; and we will use the SELECT command to view the table.

Final output:

We will get the table as it was before using the DELETE command.

Example 2:

Let us consider another table named student_table, which contains the fields of stu_id, stu_name, stu_gender, stu_age, stu_stream, and stu_marks.

We will use the following command to update the data of student_table,

Output:

As you can see, the marks for stu_id = 2 have been updated.

Now, we will revert the last command using the ROLLBACK command.

Final output:

We will get the table as it was before using the UPDATE command.

Example 3:

Let us consider another table named customer_table containing details of cust_id, cust_name, cust_gender, cust_age, and cust_address.

Now, we will update the data in customer_table using the following command,

Output:

You can see the updated table below.

Now, if you want to roll back only the last command because it was wrong, then it is required to use the COMMIT command before the last command to make the successful transactions permanent except for the last command.

We will revert the last command with the help of the ROLLBACK command.

Final output:

As you can see in the table, only the last command is reversed.

The examples discussed above help you understand what exactly the ROLLBACK command does.

The ROLLBACK command can be run automatically by the database management system, or it can be run manually by a developer to roll back changes if there is a problem with the database.

Conclusion:

In this article, we have studied ROLLBACK in SQL, which is basically a command that reverses the last transactions in the database. It restores the old data and discards the new transactions.