SQL update undo

Question

Is there a way we can undo a SQL update query?

Answer 1

You can't unless you ran it inside a transaction.

Depending on your DBMS transactions will be handled differently, so read the documentation. For instance with mysql here's how it goes:

START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; 

With Postresql:

BEGIN; UPDATE accounts SET balance = balance - 100.00     WHERE name = 'Alice'; -- etc etc COMMIT; 

With TSQL:

DECLARE @TranName VARCHAR(20) SELECT @TranName = 'MyTransaction' BEGIN TRANSACTION @TranName GO USE pubs GO UPDATE roysched SET royalty = royalty * 1.10 WHERE title_id LIKE 'Pc%' GO COMMIT TRANSACTION MyTransaction GO 

There are also some actions that can't be put into a transaction, but in most of the database management systems you can find these days you should be able to rollback an update.

Finally you can always undo an update if you keep a precise log of everything that happens in the database, but that's another story.

Answer 2

SQL Server doesn’t support this out of the box but in some cases it is possible to recover data by reading transaction log. This works if database was in full recovery mode and 3rd party tools like ApexSQL Log or Log Rescue are needed. There is also a way to read t-log using DBCC LOG command but it will probably turn out to be too difficult.

You can also check out following topics where this is discussed:

How can I rollback an UPDATE query in SQL server 2005?

Read the log file (*.LDF) in SQL Server 2008