update user_imports a set PatientFirstName = 'test' where
upper(patientid)=upper('234-56aw-7890');

insert into user_imports
(patientid, PatientFirstName) values ('234-56AW-7890','cool')
where patientid not in (select distinct patientid from user_imports);

I am getting the following error so I am assuming it is not possible

DBISAM Engine Error # 11949 SQL error - 'End of INSERT statement
expected, instead found 'where''


Basically what I want to do is update the record if it is there other
insert it.

Or is there a way to do this with an if else something like

select id from user_imports where where
upper(patientid)=upper('234-56aw-7890') if recordcount=0 then insert
else update?

Or something like that?

Bern

--

Bern Rudisill wrote:

> update user_imports a set PatientFirstName = 'test' where
> upper(patientid)=upper('234-56aw-7890');
>
> insert into user_imports
> (patientid, PatientFirstName) values ('234-56AW-7890','cool')
> where patientid not in (select distinct patientid from user_imports);
>
> I am getting the following error so I am assuming it is not possible
>
> DBISAM Engine Error # 11949 SQL error - 'End of INSERT statement
> expected, instead found 'where''
>
>
> Basically what I want to do is update the record if it is there other
> insert it.
>
> Or is there a way to do this with an if else something like
>
> select id from user_imports where where
> upper(patientid)=upper('234-56aw-7890') if recordcount=0 then insert
> else update?
>
> Or something like that?
>
> Bern

what I want is something like this

if exists(select * from user_imports where patientid)='234-56-7890'))
then
 update user_imports set PatientFirstName = 'test' where
patientid='234-56-7890';
else
 insert into user_imports (patientid, PatientFirstName) values
('234-56-7890','cool');
end if;

--

Bern


You have to use JOIN because DBISAM won't support the sub select. I'd give you an example but I use it so rarely that I have to spend about an hour figuring it out each time.

Roy Lambert

Bern

> update user_imports a set PatientFirstName = 'test' where
> upper(patientid)=upper('234-56aw-7890');
>
> insert into user_imports
> (patientid, PatientFirstName) values ('234-56AW-7890','cool')
> where patientid not in (select distinct patientid from user_imports);
>
> I am getting the following error so I am assuming it is not possible
>
> DBISAM Engine Error # 11949 SQL error - 'End of INSERT statement
> expected, instead found 'where''
>

What about using a temporary table eg

update user_imports set PatientFirstName = 'test' where
upper(patientid)=upper('234-56aw-7890'); /* update if found */
select patientid,patientfirstname into memory\temp from user_imports where
1=2; /* create temporary table */
insert into memory\temp values ('234-56AW-7890','cool'); /* add data */
insert into user_imports (patientid, PatientFirstName)
select patientid, PatientFirstName from memory\temp t where t.patientid not
in (select patientid from user_imports);
/* insert if not found */


John

Bern Rudisill wrote:

> update user_imports a set PatientFirstName = 'test' where
> upper(patientid)=upper('234-56aw-7890');
>
> insert into user_imports
> (patientid, PatientFirstName) values ('234-56AW-7890','cool')
> where patientid not in (select distinct patientid from user_imports);
>
> I am getting the following error so I am assuming it is not possible
>
> DBISAM Engine Error # 11949 SQL error - 'End of INSERT statement
> expected, instead found 'where''
>
>
> Basically what I want to do is update the record if it is there other
> insert it.
>
> Or is there a way to do this with an if else something like
>
> select id from user_imports where where
> upper(patientid)=upper('234-56aw-7890') if recordcount=0 then insert
> else update?
>
> Or something like that?
>
> Bern

I suppose another way I could do it is issue the update, then check to
see how many records it affected, if 0 then do an insert.

of is it quicker to issue a select to see if the record exist then
issue a update or an insert based on the result (there will always be 2
querys ran, one to check for the record and one to update or insert)




--

Bern,

Sorry about missing your first question.  I somehow skipped it the other
day.

<< I suppose another way I could do it is issue the update, then check to
see how many records it affected, if 0 then do an insert.

of is it quicker to issue a select to see if the record exist then issue a
update or an insert based on the result (there will always be 2 querys ran,
one to check for the record and one to update or insert) >>

I would do the former and try the update, using an insert if RowsAffected
was 0.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Bern,
>
> Sorry about missing your first question.  I somehow skipped it the
> other day.
>
> << I suppose another way I could do it is issue the update, then
> check to see how many records it affected, if 0 then do an insert.
>
> of is it quicker to issue a select to see if the record exist then
> issue a update or an insert based on the result (there will always be
> 2 querys ran, one to check for the record and one to update or
> insert) >>
>
> I would do the former and try the update, using an insert if
> RowsAffected was 0.

Thanks that is exactly what I did and it is working perfectly!!!!


--