Modify Data Using a View with Custom Column Names

Update Data through a View with Column Aliases

Write a PostgreSQL query to update a record via a view that uses column aliases for clarity.

Solution:

-- Update the salary through the EmpDetails view with renamed columns.
UPDATE EmpDetails  -- This view renames employee_id as id and salary as emp_salary.
SET emp_salary = emp_salary + 500  -- Increase the salary by 500.
WHERE id = 104;  -- Target the specific employee.

Explanation:

• Purpose of the Query:
• The goal is to update an underlying table through a view that presents columns with more user-friendly names.
• This demonstrates that column aliases in views do not hinder update operations.
• Key Components:
• UPDATE EmpDetails : Specifies the view with aliased column names.
• SET emp_salary = emp_salary + 500 : Modifies the salary value.
• WHERE id = 104 : Filters the specific row to update.
• Real-World Application:
• Enhances readability for end users while still allowing data modifications.

Notes:

• The view must be inherently updatable; the aliasing does not affect the update logic.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee's email via a view that renames the original column to contact_email using column aliases.
• Write a PostgreSQL query to update the product price using a view with aliased column names to simplify complex expressions.
• Write a PostgreSQL query to update the join date of a customer through a view that aliases the date column as signup_date for clarity.
• Write a PostgreSQL query to update a service's status via a view with column aliases that provide more intuitive column names.

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

Previous PostgreSQL Exercise: Refresh a Materialized View after Underlying Updates.
Next PostgreSQL Exercise: Update a View with Computed Columns by Modifying Base Data.