Updating Views

In SQLite, views are read-oriented by default.

If you need to change data, you typically write to base tables directly.

Core Concepts

flowchart LR
  A[Attempt INSERT/UPDATE/DELETE on view] --> B{SQLite view writable by default?}
  B -->|No| C[Operation fails]
  D[Write to base table] --> E[View reflects updated data]

Operation target	Typical result in SQLite
View (UPDATE my_view ...)	Error (read-only view)
Base table (UPDATE my_table ...)	Success (if valid statement)

Direct View Update

-- This attempt fails in default SQLite view behavior.
UPDATE v_orders_upd
SET total = 200
WHERE order_id = 1;

Expected output
Error indicating the view is not directly updatable.

Update Base Table

UPDATE orders_upd
SET total = 200
WHERE order_id = 1;

Expected output
Base table row updated successfully.

Code Examples

-- Setup base table and view.
CREATE TABLE orders_upd (
  order_id INTEGER PRIMARY KEY,
  total    REAL NOT NULL
);

INSERT INTO orders_upd (order_id, total)
VALUES (1, 50.0), (2, 90.0);

CREATE VIEW v_orders_upd AS
SELECT order_id, total
FROM orders_upd;

Expected output
Table and view created with two rows.

-- Correct write path: update base table.
UPDATE orders_upd
SET total = 200
WHERE order_id = 1;

Expected output
One base-table row updated.

-- Read through the view to confirm change.
SELECT order_id, total
FROM v_orders_upd
ORDER BY order_id;

order_id	total
1	200.0
2	90.0

SQLite-Specific Nuances

SQLite Nuance

SQLite views are read-only unless you define INSTEAD OF triggers on the view.

Trigger-based writable views are covered later in the triggers module.

Common Pitfalls / Best Practices

Pitfall

Designing application writes against views and discovering failures at runtime.

Best Practice

Treat views as read interfaces; keep writes on base tables unless you explicitly implement trigger-based behavior.

Quick Challenge

Set total = 120 for order_id = 2 and confirm via v_orders_upd.

View Solution

UPDATE orders_upd
SET total = 120
WHERE order_id = 2;

SELECT order_id, total
FROM v_orders_upd
WHERE order_id = 2;

order_id	total
2	120.0