---
title: "SQL- How to Create Updatable and Read-only Views"
description: "You can write fantastic SQL in this way so that you can avoid updatable VIEWs in your project. Two rules you can blindly use to determine if your VIEW is Read-only or Updatable in DB2. The most important point is you can UPDATE Read-only Views with this approach such as INSTEAD OF triggers."
url: https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/
date: 2016-08-28
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2016/08/how-to-prevent-updatable-views.jpg?fit=1200%2C700&ssl=1
categories: ["DB2"]
tags: ["How to write SQL to avoid updatable VIEWS", "Updatable views in DB2"]
type: post
lang: en
---

# SQL- How to Create Updatable and Read-only Views

[A View](https://srinimf.com/2023/11/02/how-to-create-a-view-in-db2-an-example-sql/) looks like a table – logical in nature. It does not store data on its own stand. The data you see in a view is come from the base table.

In three ways, you can create views – On existing tables, on Views, or some combination of the two. Defining View from another View is called Nested View. You need SQL skills to [create a](https://srinimf.com/2026/07/07/how-to-create-a-subagent-in-claude-code-a-step-by-step-guide/) view, and you can find these in any database. **The databases are DB2, ORACLE, and MYSQL. If a View is updatable or not depends on the View definition.**

## [How to](https://srinimf.com/2021/10/16/how-to-resolve-206-sql-error-object-not-valid-in-db2/) Create a VIEW

```
CREATE VIEW NONFICTIONBOOKS
AS SELECT * FROM BOOKS
WHERE BOOKTYPE = 'N';
```

## How to Create Read-only or Updatable View

- View structure decides if it is read-only or updatable
- The SELECT statement of a view determines View is read-only or updatable.
- If the rows of a view can be mapped to rows of the base table, then the view is updatable.
- **The view** NONFICTIONBOOKS defined above is updatable because each row in the view is a row in the base table.

## Rules to Create a View

- It depends on the SELECT statement definition.
- Views that use VALUES, DISTINCT, or any form of join is not updatable.

## How to Get Information from Catalog Tables

You can get information about view is updatable or not by looking at the READONLY column of the VIEWS **system-catalog table**: **Y** means it is read-only and **N** means it is not.

**How to Prevent Updating a View**

Below is the sample SQL View create Query:

```
CREATE VIEW NONFICTIONBOOKS 
AS SELECT * FROM BOOKS 
WHERE BOOKTYPE = 'N' 
WITH CHECK OPTION;
```

It restricts the user from seeing only non-fiction books. Also, it prevents the user from inserting rows that do not have a value of “N” in the BOOKTYPE column and updating the value of the BOOKTYPE column in existing rows to a value other than N.

## How to UPDATE Read-only View

- There is a mechanism to allow the appearance of updating data through a read-only view: INSTEAD OF triggers.
- These triggers can be defined on a view to intercept UPDATE, INSERT, and DELETE against a view, and instead perform actions against other tables, most commonly the base tables the view is built upon.

**Related Posts**

- [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 Questions on Primary Key, View , Static SQL and Dynamic SQL](https://srinimf.com/2015/08/18/db2-questions-on-primary-key-view-static-sql-and-dynamic-sql/)DateAugust 18, 2015
- [8 Top Rules to Write Updatable-view](https://srinimf.com/2015/11/22/2-types-of-view-updatable-and-read-only-advanced-sql/)DateNovember 22, 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%2F2016%2F08%2F28%2Favoid-updatable-views-in-db2-with-fantastic-sql%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2016%2F08%2Fhow-to-prevent-updatable-views.jpg%3Ffit%3D1200%252C700%26ssl%3D1&description=SQL-%20How%20to%20Create%20Updatable%20and%20Read-only%20Views)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2016/08/28/avoid-updatable-views-in-db2-with-fantastic-sql/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20SQL-%20How%20to%20Create%20Updatable%20and%20Read-only%20Views&body=https%3A%2F%2Fsrinimf.com%2F2016%2F08%2F28%2Favoid-updatable-views-in-db2-with-fantastic-sql%2F&share=email)
-

### Like this:

Like Loading…