Undo Updates Using Transaction Rollback

Transaction with ROLLBACK on Error

Write a PostgreSQL query to update multiple records within a transaction and then rollback if an error is detected.

Solution:

-- Begin the transaction.
BEGIN;

-- Update department for employees transitioning from Sales to Marketing.
UPDATE Employees -- Specify the table to update.
SET department = 'Marketing' -- Set the new department.
WHERE department = 'Sales'; -- Identify the affected rows.

-- Rollback the transaction to undo the changes (simulate error handling).
ROLLBACK;

Explanation:

• Purpose of the Query:
• The goal is to demonstrate how to undo changes by rolling back a transaction.
• It simulates error handling within a transactional block.
• Key Components:
• BEGIN : Starts the transaction block.
• UPDATE Employees ... : Executes the update operation.
• ROLLBACK : Reverts all changes made in the transaction.
• Real-World Application:
• Useful in scenarios where an unexpected error occurs and changes must be undone immediately.

Notes:

• In a real application, the rollback would be triggered by error detection rather than a manual command.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update inventory levels in the Inventory table and rollback if any resulting quantity is negative.
• Write a PostgreSQL query to update the account balance in the Accounts table and rollback if the new balance is below zero.
• Write a PostgreSQL query to insert a new order and update the customer's credit limit, then rollback if the credit limit is exceeded.
• Write a PostgreSQL query to update multiple rows in the Employees table and manually trigger a ROLLBACK to simulate error handling.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Basic Transaction with BEGIN and COMMIT.