Execute a Secure Salary Update Operation

Basic Transaction with BEGIN and COMMIT

Write a PostgreSQL query to update an employee's salary within a transaction using BEGIN and COMMIT.

Solution:

-- Begin the transaction.
BEGIN;

-- Increase salary by 10% for the employee with ID 101.
UPDATE Employees -- Specify the table to update.
SET salary = salary * 1.10 -- Increase salary by 10%.
WHERE employee_id = 101; -- Identify the target employee.

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

Explanation:

• Purpose of the Query:
• The goal is to ensure that the salary update is executed as an atomic operation.
• This demonstrates the use of transactions to group operations together.
• Key Components:
• BEGIN : Starts the transaction.
• UPDATE Employees ... : Performs the data modification.
• COMMIT : Finalizes and saves the transaction.
• Real-World Application:
• Ensures data integrity when updating sensitive information such as employee salaries.

Notes:

• If any error occurs before COMMIT, the transaction can be rolled back to maintain consistency.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee's department and salary within a transaction using BEGIN and COMMIT.
• Write a PostgreSQL query to insert a new record into the Orders table and update a related record in the Customers table within a transaction.
• Write a PostgreSQL query to update multiple columns in the Products table within a single transaction using BEGIN and COMMIT.
• Write a PostgreSQL query to delete a record from the Archive table and insert a log entry in the Logs table within a transaction.

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

Previous PostgreSQL Exercise: BEGIN, COMMIT, ROLLBACK Exercises Home.