SQL 更新撤销
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2178460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL update undo
提问by Balaji
Is there a way we can undo a SQL update query?
有没有办法可以撤消 SQL 更新查询？
采纳答案by marcgg
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 mysqlhere's how it goes:
根据您的 DBMS 事务的处理方式不同，因此请阅读文档。例如对于mysql，它是如何进行的：
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
With Postresql:
使用Postresql：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
With TSQL:
使用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.
还有一些操作不能放入事务中，但是在大多数数据库管理系统中，您现在可以发现您应该能够回滚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.
最后，如果您保留数据库中发生的所有事情的精确日志，您始终可以撤消更新，但这是另一回事。
回答by Ken Williams
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 Logor Log Rescueare needed. There is also a way to read t-log using DBCC LOG command but it will probably turn out to be too difficult.
SQL Server 不支持这种开箱即用的功能，但在某些情况下，可以通过读取事务日志来恢复数据。如果数据库处于完全恢复模式并且需要ApexSQL Log或Log Rescue等 3rd 方工具，则此方法有效。还有一种使用 DBCC LOG 命令读取 t-log 的方法，但它可能会变得太困难。
You can also check out following topics where this is discussed:
您还可以查看以下讨论的主题：
How can I rollback an UPDATE query in SQL server 2005?
如何在 SQL Server 2005 中回滚 UPDATE 查询？
回答by Andy White
If you run the update inside a transaction, you can rollback the transaction.
如果在事务内运行更新，则可以回滚事务。
SQL Server:
SQL 服务器：
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.
另一种技术可能是向表中添加更新触发器，并且在更新记录时，将以前的值保存到“历史”表中。如果这个表被大量使用，这可能是也可能不是一个好主意。
回答by Graeme Perrow
Undo is called rollbackin SQL. Once you've done a commit, you can't undo it without getting into restoring backups.
撤消rollback在 SQL 中调用。一旦你完成了一个commit，你就不能在不恢复备份的情况下撤消它。
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.
请注意，执行回滚将撤消整个事务，这意味着自事务开始以来的每次更新、插入和删除，通常是自上次提交或回滚以来。这意味着如果您连续进行两次更新，则无法仅撤消第二次更新。一些数据库提供了允许这样做的“保存点”。
回答by lexu
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 ..
仅当您在交易中（如 marcgg 所说）或者如果您有最近的备份……以这种方式恢复确实可以使“撤消”一词更加复杂……
回答by AdaTheDev
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!).
您可以使用第三方工具，例如 Red Gate 的Log Rescue。他们的 SQL 工具包有 14 天的免费功能试用版。（假设 SQL Server 2000！）。
