Re: Use of ROWID to update a record?
Richard Lailey wrote:
} In article <3454a683.5995953@news1.alterdial.uu.net>, Nelson Fredsell
} <nfredsel@cornercap.com> writes
} >I'd like to update the primary key in a table. The key is assigned
} >by the user (not many records in this table). One option I've
} >considered is using ROWID, but apparently this attribute is
} >not passed into a RECORD LIKE variable. My other thought is to
} >pass the primary key to a variable before any changes are made,
} >but haven't had luck executing. I'd rather not implement a SERIAL
} >attribute for this small table.
} >
} >Employee table:
} >namecode (primary key, unique)
} >TEQ
} >NHF
} >JAH
} >
} >Code initiated by Edit option on a menu:
} >
} >
} > COMMAND "Edit" "Update this record."
} >
} > MESSAGE "You are now in edit mode." ATTRIBUTE (underline)
} >
} > INPUT BY NAME pr_emp.* WITHOUT DEFAULTS
} >
} > LET pr_emp.namecode = key
} >
} > UPDATE tbl9employee
} >
} > SET tbl9employee.* = pr_emp.*
} > WHERE namecode = key
} >
} > --I initially used WHERE namecode = pr_emp.namecode
} > --Then I tried WHERE ROWID = pr_emp.ROWID
} > MESSAGE "Record updated."
} >
} > NEXT OPTION "Next"
} >
} >
} >
} <snip>
} The rowid is not part of the "p_record like record.*" set. However you
} can declare a variable eg p_rowid, which must be of type integer, and
} select the rowid into it as follows:
}
} declare cursor cursor_name for
} select
} rowid ,
} record.*
} from table
}
} foreach cursor_name into p_rowid, p_record.*
} # do some stuff
}
} update table
} set * = p_record.*
} where rowid = p_rowid
} end foreach
<snip>
Nelson / Richard,
This kind situation are the reason why Informix 4GL is much popular than
any other Informix tools.
The solution is simple as below :
DECLARE update_csr CUSRSOR FOR ..... SELECT ..... FOR UPDATE
FOREACH update_csr
....
....
UPDATE table_name SET .... WHERE CURRENT OF update_csr
....
END FOREACH
This kind of simplicity is what makes me shout those people who say that
I4GL is obsolete.
A primary key is always better if you can afford one.
--
Have a nice day
Felix K. Mathews
mailto:fmathews@systems.dhl.com