Simulate Error Handling with Bulk Update Rollback

Transaction for Bulk Update with Rollback

Write a PostgreSQL query to perform a bulk update on product prices and rollback if an error is encountered.

Solution:

-- Begin the transaction.
BEGIN;

-- Increase prices by 5% for all Electronics products.
UPDATE Products
SET price = price * 1.05
WHERE category = 'Electronics';

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

Explanation:

• Purpose of the Query:
• To simulate an error scenario where a bulk update must be undone.
• Demonstrates how a rollback reverts all changes made during the transaction.
• Key Components:
• BEGIN starts the transaction.
• The UPDATE statement applies a bulk change.
• ROLLBACK undoes the update.
• Real-World Application:
• Critical during maintenance tasks or when data validation fails post-update.

Notes:

• In practice, error handling logic would determine whether to commit or rollback based on conditions.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update prices in the Products table by 5% for a specific category and rollback if any price exceeds a given limit.
• Write a PostgreSQL query to update bonus amounts in the Employees table in bulk and rollback if any bonus calculation error is detected.
• Write a PostgreSQL query to increase the stock levels in the Inventory table for a product line and rollback if the resulting stock exceeds storage capacity.
• Write a PostgreSQL query to update discount percentages for orders in the Orders table in a transaction and rollback if the total discount surpasses a threshold.

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

Previous PostgreSQL Exercise: Transaction to Insert and Update Related Records.