sqlsql-updateundo

SQL update undo

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

Solution

  • 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.