beverly@ai.cs.wisc.edu (Beverly Seavey) (05/10/89)
Is it possible to do updates in INGRES via views? The situation is that I have assigned numbers to atoms in amino acids. The numbers are sort of unfriendly, but the names of the atoms would take up much more storage. I would like to be able to set up forms that accept and show atom names in the field, but actual storage would be using the more efficient numbers. I also need to know this for ORACLE.
dmc@sei.cmu.edu (Dawn Cappelli) (05/12/89)
In article <7501@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey) writes: > > Is it possible to do updates in INGRES via views? You can update through views in INGRES, except for the following situations: - You can't update the columns in the search condition of the view definition - You can't update columns that are set functions or computations - You can't update on views that have more than one base table. This information is in the INGRES Application Developer's Training manual. However, views are theoretically for VIEWING data, and really shouldn't be used for updating. You can use the view for retrieving the data and displaying it on the form, but why not just update the base tables directly then using the acid number (which can be placed in a hidden field when the data is retrieved)? 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 Software Engineering Institute (412) 268-6170 Carnegie Mellon University Pittsburgh, PA
jeffl@sybase.Sybase.COM (Jeff Lichtman) (05/13/89)
> 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..."