1. Forum
  2. USENET
  3. comp.databases.ingres

  • [Info-ingres] An off the wall request...... updateable view

    From Allan Biggs@allanb4@iname.com to info-ingres on Wed Mar 3 17:55:24 2021
    From Newsgroup: comp.databases.ingres

    <html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>&nbsp;</div>

    <div>&nbsp;
    <div>Also sent to the OpenROAD group
    <div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
    <div style="margin:0 0 10px 0;">&nbsp;</div>

    <div name="quoted-content"><!--P {
    margin-top: 0;
    margin-bottom: 0;
    }

    <div>
    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">I have spent the day wishing that I could update a view.</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">Just in case - there is a way of achieving something similar - is there a construct out there which behaves like a view which can be updated like a table ???&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">A quick google showed that MYSQL , postgres ,sql server allow this with restrictions ....I couldn&#39;t find specifically that Ingres allows them . I have a 3 or 4 table join I saw references to views being updateable in MYSQL if there was only one table in the view.</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">Anyone know if I can update a multi join view in 11.1 ? And any issues if I can ?&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">&nbsp;</div>

    <div style="font-family: Calibri , Arial , Helvetica , sans-serif;font-size: 12.0pt;color: rgb(0,0,0);">Thanks Allan</div>
    <br/>
    &nbsp;</div>
    </div>
    </div>
    </div>
    </div></div></body></html>
    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Karl Schendel@schendel@kbcomputer.com to Ingres and related product discussion forum on Wed Mar 3 12:06:07 2021
    From Newsgroup: comp.databases.ingres


    On Mar 3, 2021, at 11:55 AM, Allan Biggs <allanb4@iname.com> wrote:

    Also sent to the OpenROAD group

    I have spent the day wishing that I could update a view.

    You can update a simple view that is just select columns from a table, and
    I think a where clause is allowed.

    The Standard allows updating a more general view, with a whole slew
    of rules and regulations about what you can and can't update; but Ingres
    has never bothered to implement it.

    Karl

    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Roy Hann@specially@processed.almost.meat to comp.databases.ingres on Wed Mar 3 17:50:58 2021
    From Newsgroup: comp.databases.ingres

    Karl Schendel wrote:


    On Mar 3, 2021, at 11:55 AM, Allan Biggs <allanb4@iname.com> wrote:

    Also sent to the OpenROAD group

    I have spent the day wishing that I could update a view.

    You can update a simple view that is just select columns from a table, and
    I think a where clause is allowed.

    The Standard allows updating a more general view, with a whole slew
    of rules and regulations about what you can and can't update; but Ingres
    has never bothered to implement it.

    What Karl said. And if the view was created WITH CHECK OPTION you can
    update it only in such a way that a successful update doesn't cause the
    updated row(s) to disappear from the view.

    The rules are much the same as for an updateable cursor.

    Intererestingly not only can you update views, Ingres allows you to
    define rules that fire when you you update a view just like rules on
    a base table. I don't think that ability is mentioned in the
    documentation but it is correct behaviour and it works. (Just don't
    ask me to look after any system where you do that to make magical
    things to happen.)

    Incidentally, in 2013 Chris Date wrote an entire book (largely
    unhindered by any thoughts of SQL): View Updating and Relational
    Theory, ISBN 978-1-449-35784-9. I see the bookmark in my copy is well
    over a quarter of the way through it...

    Roy

    --- Synchronet 3.21b-Linux NewsLink 1.2
  • From Karl Schendel@schendel@kbcomputer.com to Ingres and related product discussion forum on Wed Mar 3 13:19:32 2021
    From Newsgroup: comp.databases.ingres


    On Mar 3, 2021, at 12:50 PM, Roy Hann <specially@processed.almost.meat> wrote:

    ...

    Incidentally, in 2013 Chris Date wrote an entire book (largely
    unhindered by any thoughts of SQL): View Updating and Relational
    Theory, ISBN 978-1-449-35784-9. I see the bookmark in my copy is well
    over a quarter of the way through it...
    I applaud you for not carelessly rushing things!
    Karl
    --- Synchronet 3.21b-Linux NewsLink 1.2