SQL update undo

Is there a way we can undo a SQL upda开发者_JAVA技巧te query?

---

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.

---

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.

---

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

---

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.

---

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

---

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!).