Ensure Consistent Updates Across Related Tables

Transaction for Maintaining Data Consistency Across Multiple Tables

Write a PostgreSQL query to update multiple related tables within a transaction to ensure data consistency.

Solution:

-- Begin the transaction.
BEGIN;

-- Update the order status in the Orders table.
UPDATE Orders
SET status = 'Shipped'
WHERE order_id = 401;

-- Update the shipment details in the Shipments table.
UPDATE Shipments
SET shipped_date = '2025-03-02'
WHERE order_id = 401;

-- Commit the transaction to apply all changes.
COMMIT;

Explanation:

• Purpose of the Query:
• To synchronize updates across tables that share related data.
• Ensures that status changes in orders are reflected in shipment records.
• Key Components:
• Two UPDATE statements modifying different tables.
• BEGIN and COMMIT to group the operations as a single unit.
• Real-World Application:
• Critical for maintaining data integrity in systems with complex relational data.

Notes:

• Transactions help prevent partial updates, ensuring that related changes are committed together.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update a user's profile in the Users table and simultaneously update related statistics in the UserStats table within a transaction.
• Write a PostgreSQL query to update the order status in the Orders table and insert a record into the OrderHistory table in one transaction.
• Write a PostgreSQL query to update product details in the Products table and update corresponding stock levels in the Inventory table within a single transaction.
• Write a PostgreSQL query to update a student's record in the Students table and update their enrollment status in the Enrollments table in one transaction.

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

Previous PostgreSQL Exercise: Transaction to Update and Delete in One Operation.