CODEWITHSUNDEEP
mysql
Naveen
Naveen

Reputation: 687

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.

Upvotes: 20

Views: 73526

Answers (4)

UsamaAmjad
UsamaAmjad

Reputation: 4604

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

Upvotes: 10

Michael Durrant
Michael Durrant

Reputation: 96484

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.

Upvotes: 11

user610217
user610217

Reputation:

If you committed your transaction, it's time to dust off that backup, sorry. But that's what backups are for. I did something like that once myself... once.

Upvotes: 13

user158017
user158017

Reputation: 2991

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?

Upvotes: 12

Related Questions