Execute Multiple DML Operations Atomically

Multi-Operation Transaction with COMMIT

Write a PostgreSQL query to perform multiple DML operations (INSERT, UPDATE, DELETE) within a single transaction.

Solution:

-- Begin the transaction.
BEGIN;

-- Insert a new employee record.
INSERT INTO Employees (employee_id, name, department, salary)
VALUES (102, 'Jane Doe', 'HR', 60000);

-- Update the salary for an existing employee.
UPDATE Employees
SET salary = 65000
WHERE employee_id = 101;

-- Delete an obsolete employee record.
DELETE FROM Employees
WHERE employee_id = 105;

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

Explanation:

• Purpose of the Query:
• To group multiple data manipulation operations into one atomic transaction.
• Ensures that all operations either succeed or fail as a unit.
• Key Components:
• BEGIN and COMMIT : Define the transaction boundaries.
• INSERT, UPDATE, DELETE : Perform various data modifications.
• Real-World Application:
• Critical for batch processing or scenarios requiring coordinated changes across multiple records.

Notes:

• A failure in any operation can trigger a rollback, preserving data integrity.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to insert a new order, update the customer's last order date, and adjust inventory levels, then commit all changes.
• Write a PostgreSQL query to insert a record into the Products table, update its stock in the Inventory table, and delete outdated product data, all in one transaction.
• Write a PostgreSQL query to insert a new employee, update the corresponding department record, and delete a temporary record from a staging table within a single transaction.
• Write a PostgreSQL query to perform an INSERT into Orders, an UPDATE in Customers, and a DELETE from Promotions in a single transaction, then commit.

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

Previous PostgreSQL Exercise: Multi-Operation Transaction with COMMIT.