SQL UNDO

What is the SQL UNDO command?

Reverses all uncommitted changes in the current transaction, effectively cancelling it.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNDO:

SQL UNDO Full Explanation

UNDO is a non-standard, vendor-specific command that behaves like ROLLBACK: it discards all data modifications made since the current transaction began and releases any locks held. Some interactive SQL shells (IBM Informix, Teradata BTEQ, Firebird, older Sybase tools) accept UNDO or UNDO WORK as a shorthand for ROLLBACK. Because UNDO is not part of the SQL standard, most modern database engines (PostgreSQL, MySQL, SQL Server, Oracle, SQLite) do not recognise it; they require the portable ROLLBACK statement instead. Attempting to run UNDO in those systems raises a syntax error. Use UNDO only when you are certain the target client or DBMS supports it. Once a transaction has been committed, neither UNDO nor ROLLBACK can reverse it. For granular reversal inside a transaction, SAVEPOINT and ROLLBACK TO SAVEPOINT are preferred and portable.

SQL UNDO Syntax

UNDO;
-- or, in some tools
UNDO WORK;

SQL UNDO Parameters

Example Queries Using SQL UNDO

-- Example in an Informix or Teradata session
BEGIN WORK;
UPDATE accounts SET balance = balance - 100 WHERE id = 10;
UPDATE accounts SET balance = balance + 100 WHERE id = 20;
-- Detect an error and cancel the transaction
UNDO;

-- Using UNDO WORK keyword variant
BEGIN WORK;
DELETE FROM orders WHERE order_date < '2023-01-01';
UNDO WORK;

Expected Output Using SQL UNDO

All changes made in the current transaction are rolled back
Tables return to the state that existed before BEGIN/START TRANSACTION was issued
Locks are released

Use Cases with SQL UNDO

Quickly cancel an in-progress transaction when you notice incorrect data changes
Interactive SQL sessions in tools that accept UNDO as a convenience keyword
Teaching or legacy scripts originally written for Informix or Teradata environments

Common Mistakes with SQL UNDO

Assuming UNDO is universally supported; it will fail on PostgreSQL, MySQL, SQL Server, Oracle, or SQLite
Running UNDO after a COMMIT and expecting data to revert
Believing UNDO can selectively roll back part of a transaction; use SAVEPOINT for that
Forgetting that UNDO also releases locks, which may affect concurrency tests

First Introduced In

IBM Informix SQL (early 1990s)

Frequently Asked Questions

What is the difference between UNDO and ROLLBACK?

Both reverse uncommitted changes, but ROLLBACK is portable and defined by the SQL standard, while UNDO is a vendor-specific alias.

Why does my database reject the UNDO command?

Most engines do not implement UNDO. Use ROLLBACK instead or consult your DBMS documentation for supported keywords.

Can UNDO reverse a committed transaction?

No. Once COMMIT is executed, data changes are permanent. You would need point-in-time recovery techniques, such as backups or flashback features, to restore previous states.

How do I undo only part of a transaction?

Define a SAVEPOINT before the section you might cancel, then issue ROLLBACK TO SAVEPOINT savepoint_name; this is supported by all major databases.