Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!wuarchive!sdd.hp.com!mips!pacbell.com!pacbell!rtech!ingres!ingres.com!billc
From: billc@ingres.com (Bill Coffin, x3387)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <1991May15.171152.557@ingres.Ingres.COM>
Date: 15 May 91 17:11:51 GMT
References: <24334@oolong.la.locus.com>
Lines: 27

> As for the duplicate row issue, don't you know that relational data
> bases aren't supposed to have duplicate rows??? :-)

But a cursor can be opened on a query that returns duplicate rows.

There are other problems in UPDATE/DELETE through a cursor.  That's why ANSI SQL86 has some
very strict rules about updatability of cursors.  For instance, what does it mean to
update a cursor if there's an aggregate function in the query?  Or any data derived by
mathematical operations, or, in fact, any data that is not a 1-1 representation of the
data in the queried table?  You generally can't update/delete through a join, but that's
a whole 'nother can of worms.  In fact, cursor UPDATE/DELETE has all the problems of
updating through views, and then some more.

Another anomaly to consider is the ORDER BY.  What does it mean to update one of the 
fields that you sorted on?  (SQL86 gets around this by stating that the query produces
a "snapshot" of the data, which you can then perturb.  This model is insufficient for solving
many UPDATE/DELETE ambiguities, but may be enough for the ORDER BY problem.  Just look 
out for the "Halloween Problem"!)

I could go on.  I once implemented a cursor interface to an SQL dialect that didn't
support cursors.  I found it was very difficult unless I enforced the existence of a 
unique index.  (The rowid was not reliable in this particular environment.)
A unique ID does solve a large number of problems.
--

Bill Coffin, aka billc@ingres.com 
 FETCH INTO :standard_disclaimer, :gratuitous_witticism, :obscure_quote