Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!sdd.hp.com!spool.mu.edu!news.nd.edu!mentor.cc.purdue.edu!tmp
From: tmp@mentor.cc.purdue.edu (Tom Putnam)
Newsgroups: comp.databases
Subject: Re: how to implement cursor based UPDATE/DELETE
Message-ID: <12260@mentor.cc.purdue.edu>
Date: 13 May 91 15:43:09 GMT
Article-I.D.: mentor.12260
References: <22626@shlump.lkg.dec.com>
Reply-To: tmp@mentor.cc.purdue.edu (Tom Putnam)
Organization: Purdue University
Lines: 46

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 4GL:

	declare reseq_c cursor for
		select rowid, * from what 
		where what.sid = this_sid and what.cid = this_cid
		order by internal, regdate, regseq

	open reseq_c
	let i = 1
	foreach reseq_c into this_row, wtmp.*

		update what set regseq = i
			where rowid = this_row
		let i = i + 1

	end foreach
	close reseq_c

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
Internet: tmp@mentor.cc.Purdue.EDU       Purdue University Computing Center
  BITNET: TMP@PURCCVM                    Mathematical Sciences Bldg.
   Phone: (317) 494-1787                 West Lafayette, IN 47907