Update Specific Records Through a Subquery-Based View

Update Data Through a View with a Subquery Condition

Write a PostgreSQL query to update the salary for senior employees via a view that selects employees based on a subquery

Solution:

-- Update the salary of senior employees via the SeniorEmployees view.
UPDATE SeniorEmployees  -- The view selects employees with age > 50.
SET salary = salary * 1.05  -- Increase salary by 5%.
WHERE employee_id = 106;  -- Identify the specific senior employee

Explanation:

• Purpose of the Query:
• The goal is to update data through a view that uses a subquery condition to filter rows (e.g., employees older than 50).
• This demonstrates that even views with more complex definitions can be updatable if they reference a single base table.
• Key Components:
• UPDATE SeniorEmployees : Specifies the view with a subquery filter.
• SET salary = salary * 1.05 : Applies the salary increase.
• WHERE employee_id = 106 : Targets a particular record.
• Real-World Application:
• Ideal for HR systems where special adjustments are made for senior employees.

Notes:

• The view must be inherently updatable (e.g., not using aggregates) for the update to succeed.
• Verify that the subquery condition does not interfere with the view’s updateability.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee's bonus via a view that selects eligible employees using a subquery based on performance metrics.
• Write a PostgreSQL query to update a manager's commission through a view that filters records using a subquery for meeting sales targets.
• Write a PostgreSQL query to update an order’s status via a view that employs a subquery to filter orders exceeding a specific value.
• Write a PostgreSQL query to update a product's discount via a view that uses a subquery to determine the average price of similar products.

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

Previous PostgreSQL Exercise: Update Data Through a View Using a Rule.