Synchronize Update and Deletion within One Transaction

Transaction to Update and Delete in One Operation

Write a PostgreSQL query to update a record and delete a related record within a single transaction.

Solution:

-- Begin the transaction.
BEGIN;

-- Update the employee's department.
UPDATE Employees
SET department = 'Operations'
WHERE employee_id = 102;

-- Delete an obsolete department record.
DELETE FROM Departments
WHERE department_name = 'Temporary';

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

Explanation:

• Purpose of the Query:
• To coordinate changes across related tables by performing an update and a deletion in one transaction.
• Ensures that both operations are committed together to maintain consistency.
• Key Components:
• UPDATE Employees ... : Modifies employee data.
• DELETE FROM Departments ... : Removes an outdated record.
• BEGIN and COMMIT : Maintain transactional integrity.
• Real-World Application:
• Useful in scenarios where data cleanup and updates need to be synchronized.

Notes:

• Using transactions in multi-table operations reduces the risk of data mismatches.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee's department in the Employees table and delete a duplicate record from the TempEmployees table within a transaction.
• Write a PostgreSQL query to update the price of a product in the Products table and delete an associated obsolete discount record in the Discounts table within a transaction.
• Write a PostgreSQL query to update a customer's contact information in the Customers table and delete an old contact record from the Contacts table within a transaction.
• Write a PostgreSQL query to update the status of an order in the Orders table and delete a corresponding notification record from the Notifications table in a single transaction.

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

Previous PostgreSQL Exercise: Transaction for Inserting a New Order and Updating Inventory.