Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!elroy.jpl.nasa.gov!decwrl!deccrl!news.crl.dec.com!nntpd.lkg.dec.com!refine.enet.dec.com!saunders
From: saunders@refine.enet.dec.com (Kyle Saunders)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <22637@shlump.lkg.dec.com>
Date: 13 May 91 18:42:43 GMT
Sender: newsdaemon@shlump.lkg.dec.com
Organization: Digital Equipment Corporation
Lines: 45


In article <12260@mentor.cc.purdue.edu>, tmp@mentor.cc.purdue.edu (Tom Putnam) writes...
|In article <22626@shlump.lkg.dec.com> saunders@refine.enet.dec.com 
|(Kyle Saunders) writes:
|>
|>	I was wondering how you would implement cursor based UPDATE's and 
|>DELETE's.
|>
|>	I have given some thought to this and I cannot see how the RDBMS can
|>properly update the correct row given the fact that the table to be updated
|>may not have _any_ indexes, and not all fields are guarenteed to be included
|>in the query.  This means there is no way to uniquely determine which row in
|>the original base table is being updated.
| 
|I recently encountered this problem in a slightly different context: I wanted
|to do an "ordered update".  My database keeps a list of people who have
|registered for a course and a counter in each record which denotes the order of
|registration.  The permits me to implement a "waiting list" when a course
|fills.  In this context, I can't declare the cursor for UPDATE since I want it
|ordered.  The work-around was to recognize that (at least under INFORMIX),
|there is a field called ROWID for each row.  I can select it, then use it to
|give me an "absolute pointer" to a record.  Here is my code sequence using
	[ INFORMIX code deleted ]
| 
|Of course, one should be absolutely paranoid about ever keeping a ROWID around
|for any longer than this sort of use.  Systems need to be able to physically
|move rows to other locations as the database is updated.
|-- 

|Tom Putnam                               Assistant Director

	Well, your solution is exactly what I am trying to avoid.  I'm
_writing_ the SQL RDBMS, so an SQL work-around is not what I need.  It looks
like I will have to require an index to be created on at least one of the
field in the query definition of the cursor.
	  I want to avoid such kludges as the "hidden" ROWID column that
INFORMIX evidently has.  It doesn't provide for the physical abstraction that
the Relational Model is supposed to have.  I suppose you could say that
requiring an index also violates the relational model, but something tells me
that when you do:
DECLARE cursor-name CURSOR FOR select-query FOR UPDATE [optional-fields]
most RDBMS's will create an index on the fly if one doesn't exist on the
fields to update.  I would do this too, but I don't implement indexes yet.

	- Kyle