---
title: "8 Top Rules to Write Updatable-view"
description: "Read only views and Updatable view and its top rules useful for SQL developers."
url: https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/
date: 2015-11-22
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2015/11/postit-scrabble-to-do.jpg?fit=1880%2C1254&ssl=1
categories: ["SQL"]
tags: ["Examples of Updatable and Non-updatable View", "Non-Updatable View in SQL", "Updatable View"]
type: post
lang: en
---

# 8 Top Rules to Write Updatable-view

Here’re the eight [top rules](https://srinimf.com/2017/12/19/create-view-awesome-sql-query-to-your-project/) **for SQL view to be updatable**. Unlike base tables, **VIEWs are either updatable or read-only, but not both.** INSERT, UPDATE, and DELETE operations are allowed on updatable VIEWs and base tables, subject to any other constraints.

Related[SQL- How to Create Updatable and Read-only Views](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/)

## Updatable Views

- INSERT, UPDATE, and DELETE are not allowed on read-only VIEWs, but you can change their base tables, as you would expect.
- An updatable VIEW is one that can have each of its rows associated with exactly one row in an underlying base table.
- When the VIEW is changed, the changes pass unambiguously through the VIEW to that underlying base table.
- **[Updatable VIEWs](https://www.oracletutorial.com/oracle-view/oracle-updatable-view/)**in Standard SQL are defined only for queries that meet these criteria:

Advertisements

## Rules for SQL view to be updatable

1. Built on only one table
2. No GROUP BY clause
3. No HAVING clause
4. No aggregate functions
5. No calculated columns
6. No UNION, INTERSECT, or EXCEPT
7. No SELECT DISTINCT clause
8. Any columns excluded from the VIEW must be NULL-able or have a DEFAULT in the base table, so that a whole row can be constructed for insertion By implication, the VIEW must also contain a key of the table.

Related[DB2 SQL – Select, Insert, Delete and Update](https://srinimf.com/2015/02/16/simple-way-to-write-dml-sql-in-db2/)

In short, we are absolutely sure that each row in the VIEW maps back to one and only one row in the base table.

- Some updating is handled by the CASCADE option in the [referential integrity](https://srinimf.com/2010/05/17/db2-referential-integrity/) constraints on the base tables, not by the VIEW declaration.
- The definition of updatability in Standard SQL is actually fairly limited, but very safe.
- The database system could look at the information it has in the [referential integrity](https://srinimf.com/2017/09/21/top-two-integrity-rules-for-rdbms-relational-model/) constraints to widen the set of allowed updatable VIEWs.
- You will find that some implementations are now doing just that, but it is not common yet.
- The SQL Standard definition of an updatable VIEW is actually a subset of the possible updatable VIEWs,and a very small subset at that.
- The major advantage of this definition is that it is based on syntax and not semantics.

**SQL Query for Updatable-view **

Related[Creating a View in DB2: Step-by-Step Guide](https://srinimf.com/2023/11/02/how-to-create-a-view-in-db2-an-example-sql/)

```
CREATE VIEW Foo1 -- updatable, has a key!
AS
SELECT *
FROM Foobar
WHERE x IN (1,
            2);
```

**SQL Query for Non-updatable View**

```
CREATE VIEW Foo2 -- not updatable!
AS
SELECT *
FROM Foobar
WHERE x = 1
UNION ALL
SELECT *
FROM Foobar
WHERE x = 2;
```

Related Posts

- [SQL- How to Create Updatable and Read-only Views](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/)DateAugust 28, 2016
- [Creating a View in DB2: Step-by-Step Guide](https://srinimf.com/2023/11/02/how-to-create-a-view-in-db2-an-example-sql/)DateNovember 2, 2023
- [DB2 SQL – Select, Insert, Delete and Update](https://srinimf.com/2015/02/16/simple-way-to-write-dml-sql-in-db2/)DateFebruary 16, 2015

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2015%2F11%2F22%2F2-types-of-view-updatable-and-read-only-advanced-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2015%2F11%2Fpostit-scrabble-to-do.jpg%3Ffit%3D1200%252C800%26ssl%3D1&description=8%20Top%20Rules%20to%20Write%20Updatable-view)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%208%20Top%20Rules%20to%20Write%20Updatable-view&body=https%3A%2F%2Fsrinimf.com%2F2015%2F11%2F22%2F2-types-of-view-updatable-and-read-only-advanced-sql%2F&share=email)
-

### Like this:

Like Loading…