Hi, this seems like a very simple error on your part - the type of error
that is simply too simple to notice. A simplified version of what you do:

> for select ...
> into ...
> do
>
> update ..
>
> if(row_count = 0) then
> begin
> insert ...
> end

In short, you update for every row, but only do the insert after the
'for select' loop is finished. Change to

> for select ...
> into ...
> do

BEGIN //New line

> update ..
>
> if(row_count = 0) then
> begin
> insert ...
> end

END //New line

HTH,
Set

cchristensenjr wrote:

> Hello:
>
> I am somewhat new to Firebird and cannot tell what I am doing wrong. I
> have a text file with some patient data. I have a couple of domains,
> an internal table to hold the patient data and a stored procedure to
> attempt to update or insert the text into the internal table. When I
> execute the stored procedure, the routine inserts the data from the
> last physical row in the text file but only that row. If I create a
> record with a value in MR_NUM in the internal table, then the update
> part of the procedure correctly updates the name fields and if I have
> multiple MR_NUM values all the records get updated where the values
> match. The data file and domain, table and procedure definitions are
> shown below.
>
> If someone has a thought to help me out in my ignorance, I would truly
> appreciate it.
>
> 000000001waugh PATIENT 2/09/1951MWN
> 2727smiley thomas-----zz 2/10/1952M
> 000003737zarnowiecki barry------xl 2/09/1951M
> 000004350BROWNie JEFFERSON B 2/24/1956MBN
> 000014628ADAIRs KEMMIE L 4/09/1922FW
> 999999999LASTy FIRST I 2/09/1951MWN
> 000025133ACOSTAz JOE A 9/04/1991MO
> 000030001BROWNer JEFFERSON B 2/09/1952MWH
> 000030003CRAWFORDs BASIL A 0/00/0000MW
> 000030004arnoldz STEVE R 6/14/1940MW
>
> ==========================================================
> CREATE DOMAIN DATEOFBIRTH AS
> DATE
> CHECK ((VALUE IS NULL) OR ( (VALUE >= '01 Jan 1875') AND (VALUE <=
> CURRENT_DATE) ))
>
> CREATE DOMAIN IDNUM AS
> NUMERIC(9,0)
> CHECK ((value is null) or (VALUE IS NOT NULL AND VALUE >= 0))
> ==========================================================
> CREATE TABLE PATIENTS (
> MR_NUM IDNUM NOT NULL /* IDNUM = NUMERIC(9,0) CHECK ((value is
> null) or (VALUE IS NOT NULL AND VALUE >= 0)) */,
> LNAME VARCHAR(50) COLLATE EN_US,
> FNAME VARCHAR(50) COLLATE EN_US,
> MNAME VARCHAR(50) COLLATE EN_US,
> DOB DATEOFBIRTH /* DATEOFBIRTH = DATE CHECK ((VALUE IS
> NULL) OR ( (VALUE >= '01 Jan 1875') AND (VALUE <= CURRENT_DATE) )) */,
> SEX CHAR(1) COLLATE EN_US,
> RACE CHAR(1) COLLATE EN_US,
> ETHNICITY CHAR(1) COLLATE EN_US
> );
>
> ==========================================================
> CREATE TABLE EXT_PATIENTS EXTERNAL 'C:\TMP\PATIENTS.TXT' (
> MR_NUM CHAR(9),
> LNAME CHAR(20),
> FNAME CHAR(12),
> MNAME CHAR(1),
> DOB CHAR(10),
> SEX CHAR(1),
> RACE CHAR(1),
> ETHNICITY CHAR(1),
> CRLF CHAR(2)
> );
>
> ==========================================================
> CREATE PROCEDURE PAT_UPDATE
> AS
> DECLARE VARIABLE WSMR_NUM INTEGER;
> DECLARE VARIABLE WSLNAME CHAR(20);
> DECLARE VARIABLE WSFNAME CHAR(12);
> DECLARE VARIABLE WSMNAME CHAR(1);
> DECLARE VARIABLE WSSEX CHAR(1);
> DECLARE VARIABLE WSRACE CHAR(1);
> DECLARE VARIABLE WSETHNICITY CHAR(1);
> begin
>
> for select cast(mr_num as integer), lname, fname, mname, sex, race,
> ethnicity from ext_patients
> into :wsmr_num, :wslname, :wsfname, :wsmname, :wssex, :wsrace,
> :wsethnicity
> do
>
> update patients set
> mr_num = :wsmr_num,
> lname = :wslname,
> fname = :wsfname
> where mr_num = :wsmr_num;
>
> if(row_count = 0) then
> begin
> insert into patients
> (mr_num, lname, fname)
> values (:wsmr_num, :wslname, :wsfname);
> end
>
> end