Suppose we executed an SQL query that modifies a table or tables in a database and incorrectly updates the wrong information due to human error. How can we reverse this? Usually, we can’t, unless we use the following commands in MySQL:

Let’s say we want to update the users table so that users who have role_id = 5 will have to get their role_id changed to 3. In order to execute this safely and give ourselves the opportunity to rollback the transaction should it fail, we would do the following:

mysql> start transaction;
mysql> update users set role_id=3 where role_id=5;
mysql> commit;

We start the transaction and then proceed to execute our query. Once we make sure our query has updated everything we want correctly, we then run commit; and press Enter. If there is a mistake, we run rollback; instead of commit; and then proceed to correct our query should that be needed before running that query again. Once the tables have been updated correctly, we then run commit;.