Bird
Raised Fist0
MySQLquery~5 mins

Updatable views in MySQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction
Updatable views let you change data through a saved query, making it easier to work with complex data without touching the original tables directly.
You want to simplify data access for users by showing only certain columns or rows.
You need to allow updates on a subset of data without exposing the full table.
You want to hide complex joins but still allow data changes.
You want to enforce security by restricting which data can be changed.
You want to keep your main tables safe but allow controlled updates.
Syntax
MySQL
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
The view must be based on a single table or a join that supports updates.
Not all views are updatable; some complex views cannot be changed directly.
Examples
Creates a view showing only active customers.
MySQL
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'active';
Updates the email of the customer with id 1 through the view.
MySQL
UPDATE active_customers SET email = 'new@example.com' WHERE id = 1;
Adds a new active customer through the view.
MySQL
INSERT INTO active_customers (id, name, email) VALUES (5, 'Anna', 'anna@example.com');
Sample Program
This example creates an employees table, inserts data, creates a view for sales department employees, updates salary through the view, and then shows the updated data in the original table.
MySQL
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

INSERT INTO employees VALUES
(1, 'John', 'Sales', 50000),
(2, 'Jane', 'HR', 55000),
(3, 'Mike', 'Sales', 52000);

CREATE VIEW sales_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

-- Update salary through the view
UPDATE sales_employees SET salary = 53000 WHERE id = 3;

-- Select to see the change
SELECT * FROM employees WHERE id = 3;
OutputSuccess
Important Notes
Views that use joins, aggregates, or groupings often cannot be updated directly.
MySQL allows updates on simple views that map directly to one table.
If a view is not updatable, you can use triggers or stored procedures to handle changes.
Summary
Updatable views let you change data through a saved query.
They simplify working with complex or sensitive data.
Not all views can be updated; simple views on one table usually can.

Practice

(1/5)
1. What is an updatable view in MySQL?
easy
A. A view that only shows data but does not allow any changes.
B. A view that allows you to change the data in the underlying table through it.
C. A view that automatically creates new tables.
D. A view that stores data separately from the original table.

Solution

  1. Step 1: Understand what a view is

    A view is a saved query that shows data from one or more tables.

  2. Step 2: Define updatable view

    An updatable view lets you change data in the underlying table through the view itself.

  3. Final Answer:

    A view that allows you to change the data in the underlying table through it. -> Option B

  4. Quick Check:

    Updatable view = allows data change [OK]
Hint: Updatable views let you edit data through the view [OK]
Common Mistakes:
  • Thinking views store data separately
  • Confusing read-only views with updatable views
  • Assuming all views are updatable
2. Which of the following is the correct syntax to create an updatable view in MySQL?
easy
A. CREATE VIEW view_name UPDATEABLE AS SELECT * FROM table_name;
B. CREATE UPDATABLE VIEW view_name AS SELECT * FROM table_name;
C. CREATE VIEW view_name WITH UPDATE AS SELECT * FROM table_name;
D. CREATE VIEW view_name AS SELECT * FROM table_name;

Solution

  1. Step 1: Recall MySQL view creation syntax

    MySQL uses CREATE VIEW view_name AS SELECT ... to create views.

  2. Step 2: Understand updatable views syntax

    There is no special keyword for updatable views; they are updatable if the SELECT is simple enough.

  3. Final Answer:

    CREATE VIEW view_name AS SELECT * FROM table_name; -> Option D

  4. Quick Check:

    Standard CREATE VIEW syntax = CREATE VIEW view_name AS SELECT * FROM table_name; [OK]
Hint: No special keyword needed; simple CREATE VIEW works [OK]
Common Mistakes:
  • Adding non-existent keywords like UPDATABLE or WITH UPDATE
  • Using wrong syntax keywords
  • Assuming updatable views need special creation syntax
3. Given the table employees(id, name, salary) and the view:
CREATE VIEW emp_view AS SELECT id, name FROM employees;
Which of the following statements will succeed?
medium
A. UPDATE emp_view SET name = 'Alice' WHERE id = 1;
B. INSERT INTO emp_view (id, name) VALUES (4, 'Bob');
C. DELETE FROM emp_view WHERE salary = 2000;
D. UPDATE emp_view SET salary = 5000 WHERE id = 3;

Solution

  1. Step 1: Check view columns and underlying table

    The view selects only id and name, excluding salary.

  2. Step 2: Determine which operations are allowed

    Updating name through the view is allowed because it maps directly to the table column.
    INSERT fails (missing salary, assuming NOT NULL no default).
    DELETE referencing salary fails because salary not in view.
    Updating salary fails because salary not in view.

  3. Final Answer:

    UPDATE emp_view SET name = 'Alice' WHERE id = 1; -> Option A

  4. Quick Check:

    Update on view columns allowed = UPDATE emp_view SET name = 'Alice' WHERE id = 1; [OK]
Hint: You can update only columns included in the view [OK]
Common Mistakes:
  • Trying to update columns not in the view
  • Assuming inserts/deletes always work on views
  • Ignoring missing columns in the view
4. You created a view with:
CREATE VIEW v AS SELECT id, name FROM employees WHERE salary > 3000;
Why does the statement UPDATE v SET salary = 5000 WHERE id = 5; fail?
medium
A. Because the view does not include the salary column.
B. Because the view has a WHERE clause, making it non-updatable.
C. Because you cannot update views with more than one column.
D. Because the view name is invalid.

Solution

  1. Step 1: Check the columns in the view and the UPDATE target

    The view selects only id and name; salary is excluded.

  2. Step 2: Check other options

    A WHERE clause does not prevent updates to included columns.
    Views can have multiple columns and be updatable.
    The view name is valid.

  3. Final Answer:

    Because the view does not include the salary column. -> Option A

  4. Quick Check:

    Cannot update column not in view [OK]
Hint: Cannot modify columns absent from the view [OK]
Common Mistakes:
  • Thinking WHERE clause blocks updates
  • Assuming multiple columns prevent updates
  • Ignoring missing columns in the view
5. You want to create an updatable view on the orders table that shows only order_id and customer_id. You also want to allow updates to customer_id through the view. Which of the following is the best approach?
hard
A. CREATE VIEW order_view AS SELECT order_id FROM orders; Then update customer_id through order_view.
B. CREATE VIEW order_view AS SELECT order_id, customer_id FROM orders WHERE order_date > '2023-01-01'; Then update customer_id through order_view.
C. CREATE VIEW order_view AS SELECT order_id, customer_id FROM orders; Then update customer_id through order_view.
D. CREATE VIEW order_view AS SELECT order_id, customer_id, total FROM orders; Then update customer_id through order_view.

Solution

  1. Step 1: Identify columns needed in the view

    The view must include customer_id to allow updates on it.

  2. Step 2: Avoid issues that limit updatability or scope

    Adding a WHERE clause (as in B) limits updates to only recent orders.
    Including extra columns (as in C) shows more than requested.
    Excluding customer_id (as in A) prevents updating it.
    D provides exactly the requested columns without restrictions.

  3. Final Answer:

    CREATE VIEW order_view AS SELECT order_id, customer_id FROM orders; Then update customer_id through order_view. -> Option C

  4. Quick Check:

    Simple view with needed columns = updatable [OK]
Hint: Include columns to update, avoid WHERE filters [OK]
Common Mistakes:
  • Adding WHERE filters that limit updates
  • Excluding columns you want to update
  • Adding unnecessary columns that confuse
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard