2
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+ 

Query:

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

After successfully executing this query, how do I back to the previous stage?

I have tried rollback, but it is not working.

My engine is InnoDB.

1
  • 1
    What's your storage engine (InnoDB, MyISAM, etc.)?
    – Mat
    Sep 23, 2013 at 13:32

1 Answer 1

4

If your table engine is not InnoDB, or you haven't used transactions for this operation, then it might be not possible.

To have possibility to ROLLBACK DML statements (like INSERT, UPDATE or DELETE queries) you should use transaction blocks:

START TRANSACTION;

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;
-- and more DML queries

COMMIT; -- or ROLLBACK;

Since transaction was COMMITed it can not be rolled back. Only option (that I see) is to restore database backup (if it exists).


UPDv1:

Just as an advice: Before you do any critical changes to database (perform transactions, execute complex scripts, that includes INSERTs, UPDATEs and DELETEs or change data structures with ALTERs or DROPs) make a backup of your database.

You may probably restore backup as another database copy, then test script on it. If nothing goes wrong, then perform script for a real database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.