Updatable views

You can use views not only to read data but also to update data. The updated views are called updatable views.

For example, you can execute the following DELETE statement on the view staff_dept_10:

DELETE staff_dept_10 WHERE employee_id = 200;

OceanBase Database performs the following steps after receiving the request:

1. Parses the statement and replaces staff_dept_10 with the definition of the view.

2. Checks whether the view, as an object to be updated, conforms to specific constraints.

3. Tries to merge the view with the DELETE statement.

4. Generates an execution plan and executes the merged statement.

The DELETE statement actually executed in OceanBase Database is:

DELETE employees WHERE employee_id = 200 and department_id = 10;

Prerequisites for modifying a view by executing an UPDATE statement:

• Each update operation modifies only one physical table.

• Each row in the modified table appears only once in the query of the view. This does not apply to many-to-many join queries.

• A modified view does not have the WITH READ ONLY clause in its definition.