Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Is this possibe? |
Thu, Apr 27 2006 11:21 PM | Permanent Link |
"Bern Rudisill" | 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 -- |
Thu, Apr 27 2006 11:46 PM | Permanent Link |
"Bern Rudisill" | 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; -- |
Fri, Apr 28 2006 2:58 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Apr 28 2006 11:55 AM | Permanent Link |
"John Hay" | 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 |
Sun, Apr 30 2006 5:27 PM | Permanent Link |
"Bern Rudisill" | 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) -- |
Mon, May 1 2006 2:50 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, May 1 2006 5:55 PM | Permanent Link |
"Bern Rudisill" | 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!!!! -- |
This web page was last updated on Monday, May 6, 2024 at 03:19 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |