In SQL, views can be updated, but not all views are updatable. The update views must meet certain criteria, including that the view must not contain any of the following:
- Aggregate functions (such as SUM, AVG, and MAX)
- GROUP BY clauses
- DISTINCT keyword
- UNION or UNION ALL operators
- Subqueries in the SELECT list or the WHERE clause
- Joins involving more than one table
If a view meets these criteria, it can be updated using the standard UPDATE, INSERT, and DELETE statements. When an update is made to the view, the underlying data in the tables that the view is based on is also updated.
Here are some examples of updating views in SQL:
- Create a view that displays the employees in a company’s sales department:
CREATE VIEW sales_employees AS
SELECT employee_id, first_name, last_name, job_title
FROM employees
WHERE department_id = 50;
- Update the job title of an employee in the sales department:
UPDATE sales_employees
SET job_title = 'Senior Sales Associate'
WHERE employee_id = 101;
- Insert a new employee into the sales department:
INSERT INTO sales_employees (employee_id, first_name, last_name, job_title)
VALUES (120, 'Sara', 'Johnson', 'Sales Associate');
- Delete an employee from the sales department:
DELETE FROM sales_employees
WHERE employee_id = 102;
Note that in each of these examples, the changes made to the view are reflected in the underlying tables. So if we were to query the employees table after executing these statements, we would see that the changes have been made there as well.Learn SQL Now: Learn From Scratch to Advanced