Re: how to implement cursor based UPDATE/DELETE
Path: emory!swrinde!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
Message-ID: <12260@mentor.cc.purdue.edu>
Date: 13 May 91 15:43:09 GMT
References: <22626@shlump.lkg.dec.com>
Reply-To: tmp@mentor.cc.purdue.edu (Tom Putnam)
Organization: Purdue University
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