Updatable Views (Rules)

Updatable Views (Rules) is a SQL statement in the Views category. Rules for updatable views: must be based on single table, no DISTINCT/GROUP BY, no computed columns in insert columns. The syntax is -- Views that allow INSERT/UPDATE/DELETE must meet certain criteria.. It returns updatable view. A typical example: -- Updatable view: CREATE VIEW vw_it_employees AS SELECT id, name, salary FROM employees WHERE department = 'IT'; -- This view IS updatable (single table, no agg) INSERT INTO vw_it_employees (id, name) VALUES (100, 'Charlie'); -- Works: inserts into employees table -- NOT updatable view: CREATE VIEW vw_dept_summary AS SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department; -- NOT updatable: contains GROUP BY and aggregate A close relative is CREATE VIEW, which creates a virtual table based on a SELECT query. Think of it as a saved query that acts like a table. A close relative is DROP VIEW, which removes a view from the database. Does not affect the underlying tables.