Updating Data Through Views

You can also update data through views, provided the view is updatable. An updatable view is one that allows you to perform INSERT, UPDATE, and DELETE operations. The view must be derived from a single table and must not contain any of the following:

• DISTINCT
• GROUP BY
• HAVING
• UNION
• JOIN
• Subqueries in the SELECT clause

Assume engineering_employees is an updatable view. You can update data as follows:

UPDATE engineering_employees
SET salary = salary + 5000
WHERE last_name = 'Doe';

Modifying a View

To modify an existing view, use the CREATE OR REPLACE VIEW statement:

CREATE OR REPLACE VIEW engineering_employees AS
SELECT first_name, last_name, salary, department
FROM employees
WHERE department = 'Engineering';

Dropping a View

To delete a view, use the DROP VIEW statement:

DROP VIEW engineering_employees;