'Undo a mysql UPDATE command

I was trying to do an update on the MySQL server and accidentally forgot to add an additional WHERE clause which was supposed to edit one row.

I now have 3500+ rows edited due to my error.

I may have a back up but I did a ton of work since the last backup and I just dont want to waste it all because of 1 bad query.

Please tell me there is something i can do to fix this.

mysql


Solution 1:[1]

Just an idea - could you restore your backup to a NEW database and then do a cross database query to update that column based on the data it used to be?

Solution 2:[2]

Nothing.

Despite this you can be glad that you've got that learning experience under your belt and be proud of how you'll now change your habits to greatly reduce the chance of it happening again. You'll be the 'master' now that can teach the young pups and quote from actual battle-tested experience.

Solution 3:[3]

There is only one thing that you can do now is FIX YOUR BAD HABIT, it will help you in future. I know its an old question and OP must have learned the lesson. I am just posting it for others because I also learned the lesson today.

So I was supposed to run a query which was supposed to update some fifty records and then MySQL returned THIS 48500 row(s) affected which gave me a little heart attack due to one silly mistake in WHERE condition :D

So here are the learnings:

  1. Always check your query twice before running it but sometimes it won't help because you can still make that silly mistake.
  2. Since Step 1 is not always helpful its better that you always take backup of your DB before running any query that will affect the data.
  3. If you are lazy to create a backup (as I was when running that silly query) because the DB is large and it will take time then at least use TRANSACTION and I think this is a good effortless way to beat the disaster. From now on this is what I do with every query that affects data:

I start the Transaction, run the Query, and then check the results if they are OK or Not. If the results are OK then I simply COMMIT the changes otherwise to overcome the disaster I simply ROLLBACK

START TRANSACTION;
  UPDATE myTable
  SET name = 'Sam'
  WHERE recordingTime BETWEEN  '2018-04-01 00:00:59' AND '2019-04-12 23:59:59'; 
ROLLBACK;
-- COMMIT; -- I keep it commented so that I dont run it mistakenly and only uncomment when I really want to COMMIT the changes

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user158017
Solution 2 Michael Durrant
Solution 3

Related Questions