At 08:19 AM 16/04/2003 +0100, you wrote:

>the yahoo groups search on this wasn't particularly useful... so at the
>expense of seeming redundant: why are views
>containing joins not updatable? i can understand (as covered by database
>theory books) why views with aggregates aren't
>updatable, etc. but with simple joins, it'd be awfully handy, for
>something like this:
>
>create view fix_stuff as select a.correct_value, b.wrong_value from a
>inner join b on b.a_fk = a.pk /* and other
>conditions about b */;
>update fix_stuff set b.wrong_value = a.correct_value where 1=1;
>
>(above comment added to note that this isn't just because i felt like
>violating 3NF in my database)
>
>using sub-selects in update statements seems awfully roundabout (and
>without indeces, not terribly fast) ... stored
>procs seem excessive, and yes, i could just write a quick app to do this
>via cursors ... but (please don't flame me)
>aren't updatable views containing joins considered at least somewhat
>necessary in a relational database? (within
>limits, understandably.)

No, they are also logically impossible. Under SQL rules, each update,
insert or delete statement can operate on one and only one table. There
isn't any syntax (or logic) that says
update tablea and table b...
and you can't issue multiple statements in one "hit".

>do you guys have any usual/preferred ways of dealing with this?

Yes, of course. You can write a stored procedure. In some cases, you
might achieve what you want with an after update trigger. Various database
access components encapsulate ways to make joined sets "updatable" at the
client.

>are views with joins -going- to be updatable in some
>future release? (i didn't see it in any of the open or closed bugs, or the
>open/closed feature requests when i checked
>earlier ... maybe i missed it?)

No, Firebird has no plans at present to be a non-SQL database. :-)

heLen