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

If you run the update inside a transaction, you can rollback the transaction.

SQL Server:

begin transaction

update [Table] set col1 = 'test' where Id = 3

rollback transaction

Another technique might be to add an update trigger to the table, and anytime the record is updated, save the previous values off into a "history" table. This may or may not be a good idea if this table is heavily used.

Answer 3

Undo is called rollback in SQL. Once you've done a commit, you can't undo it without getting into restoring backups.

Note that doing a rollback will undo an entire transaction, which means every update, insert, and delete since the transaction began, which is usually since the last commit or rollback. This means that if you do two consecutive updates, you can't undo just the second one. Some databases provide "savepoints" which do allow this.

Answer 4

Only if you are in a transaction (as marcgg) said or if you have a recent backup ... recovering that way does stratch the term "undo" rathermuch ..

Answer 5

You can use a third party tool like Red Gate's Log Rescue. There is a 14-day free, functional trial of their SQL Toolkit. (Assuming SQL Server 2000!).