> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/views/inserting-and-updating-with-views.md).

# Inserting and Updating with Views

A [view](/docs/server/server-usage/views.md) can be used for inserting or updating. However, there are certain limitations.

## Updating with Views

A view cannot be used for updating if it uses any of the following:

* `ALGORITHM=TEMPTABLE` (see [View Algorithms](/docs/server/server-usage/views/view-algorithms.md))
* [HAVING](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md)
* [GROUP BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md#group-by)
* [DISTINCT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md#distinct)
* [UNION](/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations/union.md)
* [UNION ALL](/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations/union.md)
* An aggregate function, such as [MAX()](/docs/server/reference/sql-functions/aggregate-functions/max.md), [MIN()](/docs/server/reference/sql-functions/aggregate-functions/min.md), [SUM()](/docs/server/reference/sql-functions/aggregate-functions/sum.md) or [COUNT()](/docs/server/reference/sql-functions/aggregate-functions/count.md)
* Subquery in the `SELECT` list
* Subquery in the `WHERE` clause referring to a table in the `FROM` clause
* If it has no underlying table because it refers only to literal values
* The `FROM` clause contains a non-updatdable view.
* Multiple references to any base table column
* An outer join
* An inner join where more than one table in the view definition is being updated
* If there's a `LIMIT` clause, the view does not contain all primary or not null unique key columns from the underlying table and the [updatable\_views\_with\_limit](/docs/server/server-management/variables-and-modes/server-system-variables.md#updatable_views_with_limit) system variable is set to `0`.

## Inserting with Views

A view cannot be used for inserting if it fails any of the criteria for [updating](#updating-with-views), and must also meet the following conditions:

* the view contains all base table columns that don't have default values
* no base table columns are present in view select list more than once
* the view columns are all simple columns, and not derived in any way. The following are examples of derived columns
  * column\_name + 25
  * LOWER(column\_name)
  * (subquery)
  * 9.5
  * column1 / column2

## Checking Whether a View is Updatable

MariaDB stores an IS\_UPDATABLE flag with each view, so it is always possible to see if MariaDB considers a view updatable (although not necessarily insertable) by querying the IS\_UPDATABLE column in the INFORMATION\_SCHEMA.VIEWS table.

## WITH CHECK OPTION

The WITH CHECK OPTION clause is used to prevent updates or inserts to views unless the WHERE clause in the SELECT statement is true.

There are two keywords that can be applied. WITH LOCAL CHECK OPTION restricts the CHECK OPTION to only the view being defined, while WITH CASCADED CHECK OPTION checks all underlying views as well. CASCADED is treated as default if neither keyword is given.

If a row is rejected because of the CHECK OPTION, an error similar to the following is produced:

```sql
ERROR 1369 (HY000): CHECK OPTION failed 'db_name.view_name'
```

A view with a WHERE which is always false (like `WHERE 0`) and WITH CHECK OPTION is similar to a [BLACKHOLE](/docs/server/server-usage/storage-engines/blackhole.md) table: no row is ever inserted and no row is ever returned. An insertable view with a WHERE which is always false but no CHECK OPTION is a view that accepts data but does not show them.

## Examples

```sql
CREATE TABLE table1 (x INT);

CREATE VIEW view1 AS SELECT x, 99 AS y FROM table1;
```

Checking whether the view is updateable:

```sql
SELECT TABLE_NAME,IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS;
+------------+--------------+
| TABLE_NAME | IS_UPDATABLE |
+------------+--------------+
| view1      | YES          |
+------------+--------------+
```

This query works, as the view is updateable:

```sql
UPDATE view1 SET x = 5;
```

This query fails, since column `y` is a literal.

```sql
UPDATE view1 SET y = 5;
ERROR 1348 (HY000): Column 'y' is not updatable
```

Here are three views to demonstrate the WITH CHECK OPTION clause.

```sql
CREATE VIEW view_check1 AS SELECT * FROM table1 WHERE x < 100 WITH CHECK OPTION;

CREATE VIEW view_check2 AS SELECT * FROM view_check1 WHERE x > 10 WITH LOCAL CHECK OPTION;

CREATE VIEW view_check3 AS SELECT * FROM view_check1 WHERE x > 10 WITH CASCADED CHECK OPTION;
```

This insert succeeds, as `view_check2` only checks the insert against `view_check2`, and the WHERE clause evaluates to true (`150` is `>10`).

```sql
INSERT INTO view_check2 VALUES (150);
```

This insert fails, as `view_check3` checks the insert against both `view_check3` and the underlying views. The WHERE clause for `view_check1` evaluates as false (`150` is `>10`, but `150` is not `<100`), so the insert fails.

```sql
INSERT INTO view_check3 VALUES (150);
ERROR 1369 (HY000): CHECK OPTION failed 'test.view_check3'
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/views/inserting-and-updating-with-views.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.