Update Specific Records Through a Subquery-Based View

Last update on October 29 2025 12:36:47 (UTC/GMT +8 hours)

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.

Go to:

PREV : Update Data Through a View Using a Rule.

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