Process Order and Adjust Inventory Together

Transaction for Inserting a New Order and Updating Inventory

Write a PostgreSQL query to insert a new order and update the product inventory in a single transaction.

Solution:

-- Begin the transaction.
BEGIN;

-- Insert a new order record.
INSERT INTO Orders (order_id, product_id, customer_id, order_date, quantity)
VALUES (302, 501, 20, '2025-03-02', 3);

-- Update the inventory for the ordered product.
UPDATE Inventory
SET quantity = quantity - 3
WHERE product_id = 501;

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

Explanation:

• Purpose of the Query:
• To ensure that both the order creation and inventory update are applied atomically.
• Prevents inconsistencies where an order is placed without updating inventory.
• Key Components:
• INSERT INTO Orders ... : Adds the new order.
• UPDATE Inventory ... : Adjusts the product quantity.
• BEGIN and COMMIT : Enclose the operations within a transaction.
• Real-World Application:
• Essential in retail systems where order processing and stock management must remain synchronized.

Notes:

• If an error occurs in either operation, the entire transaction can be rolled back.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to insert a new order into the Orders table and update the Inventory table to deduct the ordered quantity within a transaction.
• Write a PostgreSQL query to insert a new order, update the product stock in the Products table, and log the transaction in a SalesLog table, all within a transaction.
• Write a PostgreSQL query to add a new order, update the Inventory table, and update customer reward points, then commit all changes in a single transaction.
• Write a PostgreSQL query to insert an order into the Orders table and update the Inventory table, verifying that the inventory does not become negative before committing.

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

Previous PostgreSQL Exercise: Transaction with Conditional Check (Simulated Error Scenario).