Synchronize Order Creation with Customer Update

Transaction to Insert and Update Related Records

Write a PostgreSQL query to insert a new order and update the corresponding customer's last order date within a transaction.

Solution:

-- Begin the transaction.
BEGIN;

-- Insert a new order record.
INSERT INTO Orders (order_id, customer_id, order_date, amount)
VALUES (201, 10, '2025-03-02', 250.00);

-- Update the customer's last order date.
UPDATE Customers
SET last_order_date = '2025-03-02'
WHERE customer_id = 10;

-- Commit the transaction to finalize both operations.
COMMIT;

Explanation:

• Purpose of the Query:
• To ensure that the insertion of an order and the update of customer data occur as a single unit.
• Prevents data mismatch between orders and customer records.
• Key Components:
• INSERT INTO Orders ... : Adds a new order.
• UPDATE Customers ... : Updates related customer information.
• BEGIN and COMMIT : Maintain atomicity.
• Real-World Application:
• Common in e-commerce systems where order records must synchronize with customer activity.

Notes:

• Using transactions ensures that both changes are applied or none at all if an error occurs.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to insert a new order into the Orders table and update the Customers table with the order date in one transaction.
• Write a PostgreSQL query to insert a product review into the Reviews table and update the product's average rating in the Products table within a transaction.
• Write a PostgreSQL query to add a new blog post in the Posts table and update the Authors table to increment the post count, all in a single transaction.
• Write a PostgreSQL query to insert a new booking in the Bookings table and update the Rooms table to reflect the new booking within a transaction.

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

Previous PostgreSQL Exercise: Transaction for Fund Transfer between Accounts.