[RBG7-L] - Re: DECLARE CURSOR

[A. Razzak Memon]

At 12:42 PM 7/26/2004 -0400, Bill Owens wrote:

If anyone can help please.

I've never used declare curor I've always used set pointer..
I'm trying to read plotowners table and get ownerid and seqno from table
then update burial table with ownerid where seqno in plotowner table = seqno
in burial table

code below is not working...

SET MESSAGES Off
SET ERROR MESSAGES ON
SET RULES OFF
SET VAR Vowner INTEGER
set var vseq integer
declare cursor1 cursor for select ownerid, seqno from plotowners order by
seqno
open cursor1
fetch cursor1 into vowner indicator vi1, vseqno indicator vi2
while sqlcode <> 100 then
update burials set ownerid = .vowner where seqno = .vseq and where current
of cursor1
fetch cursor1 into vowner indicator vi1, vseqno indicator vi2
endwhile
drop cursor1
quit

Bill,

Here's the syntax for correlational update:

UPDATE <TableToUpdate> SET <ColumnToUpdate> = <LookupColumnName> +
FROM <LookupTableName>,<TableToUpdate> +
WHERE <TableToUpdate.ColumnToUpdate> = <LookupTableName.LookupColumnName>

So, in your case, try the following:

UPDATE Burials SET OwnerID = OwnerID +
FROM PlotOwners,Burials +
WHERE Burial.SeqNo = PlotOwners.SeqNo

Hope that helps!

Very Best R:egards,

Razzak.