Path: utzoo!attcan!utgpu!utstat!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!ucbvax!mtxinu!sybase!jeffl@sybase.Sybase.COM
From: jeffl@sybase.Sybase.COM (Jeff Lichtman)
Newsgroups: comp.databases
Subject: Re: Update in INGRES via view
Message-ID: <4235@sybase.sybase.com>
Date: 13 May 89 04:33:09 GMT
References: <3352@fp.sei.cmu.edu>
Sender: news@sybase.sybase.com
Lines: 42

> I believe that updating through views is not legal in ANSI standard SQL, 
> so you're better off avoiding that capability, even though it does exist.
> -- 
> Dawn Cappelli				dmc@sei.cmu.edu

Nope.  ANSI SQL allows updates through views, with rules that are similar
to but not the same as the QUEL rules.  A major difference is that SQL
allows you to update columns that are in the where clause of the view
definition, unless the view is created with the "check option".  The
rationale for disallowing such updates is to prevent the user from updating
a view in such a way that the row would "disappear" from the view.

For example, suppose the view is created with the following where clause:

		where x.y = 3

Then suppose you do the following update:

		update myview set x = 4 where ...

If this update is allowed, the rows that go into the view will no longer
match the where clause, so you will no longer be able to select them from
the view.  If you want your view to act as much as possible like a real table,
this is a bad thing: you can put rows into the view that "disappear", which
is not how real tables act.

In standard SQL, if you create a view with the "check option", it acts sort
of like QUEL views when you update the columns in the where clause, but
with an important difference.  In QUEL, it won't allow you to update any
column in the where clause of the view, no matter what value you put in.
In SQL with the "check option", it allows you to update such a column if
it matches the where clause.  For instance, in the example above, it would
allow you to do the following:

		update myview set x = 3 where ...

because when you select from the view, the row will show up.  Such an update
would be disallowed in QUEL, because it doesn't pay attention to the value.
---
Jeff Lichtman at Sybase
{mtxinu,pacbell}!sybase!jeffl  -or- jeffl@sybase.com
"Saints should always be judged guilty until they are proved innocent..."