Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!elroy.jpl.nasa.gov!decwrl!pa.dec.com!decuac!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: <22653@shlump.lkg.dec.com>
Date: 14 May 91 13:05:20 GMT
Sender: newsdaemon@shlump.lkg.dec.com
Organization: Digital Equipment Corporation
Lines: 55


In article <24334@oolong.la.locus.com>, jfr@locus.com (Jon Rosen) writes...
|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 
|>>DELETEs. I have given some thought to this and 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.
|><some stuff deleted...>
|>....  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.
|><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.
| 
|Kyle questions how cursor-based UPDATEs and DELETEs can be implemented.  
|If this refers to how an RDBMS implements it, the answer is related
|to Tom's response... I.e., internally, the RDBMS must keep some kind of
|a relative row id that it uses to link the cursor-retrieved row back to
|the actual data row.  In fact, this implementation is quite common.  It
|even leads to those databases such as Informix and Oracle actually
|materializing the ROWID into a user-accessible column which can be 
|used to do direct-access updates against rows without using RDBMS cursors
|and the UPDATE/DELETE WHERE CURRENT OF statement.                  

	Keeping an explicit pointer for each row generated by an OPEN cursor
to the original base table is a _major_ kludge IMHO.  That's special casing
this action.  What's worse is letting users access this ROWID.  If ROWID is
provided in any SELECT, why?!?  Users will abuse this bit of information.  The
user should not see the underlying physical implementation of the Relational
Model, if at all possible.

|Also, it is not as bad as Tom thinks to keep using this ROWID.  Usually,
|the ROWID is a logical construction and has nothing (or little) to do
|with the physical location/inode/record number of the actual row.  So 

	How is it a logical construction?  How is it safe to be anything _but_
the actual record number (guarenteed to be unique without having to look-up or
generate anything)?

	[ stuff about integrity deleted ]

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

	I never mentioned duplicate rows.  

|Jon Rosen

	- Kyle