Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Duplicate key value |
Thu, Mar 23 2006 3:39 PM | Permanent Link |
"Scott Martin" | Is there a method to determine the value of the duplicate key when the error triggered?
(in this example, DuplicateKeyValue) If not, is there a better way to perform the following? Importing new inventory into existing inventory, adding new, update qty on existing. Regards, Scott. // primary index on StockSerial with qryTemp do begin if Active then Close; SQL.Clear; SQL.Text := 'INSERT INTO Inventory SELECT * FROM SWFY'; try ExecSQL; Close; except on E: Exception do begin // error 9729 duplicate key found , DBISAM_KEYVIOL SQL.Clear; SQL.Text := 'UPDATE Inventory SET Qty = 2 WHERE StockSerial = '+QuotedStr(DuplicateKeyValue); try ExecSQL; Close; except end; end; end; end; |
Thu, Mar 23 2006 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< Is there a method to determine the value of the duplicate key when the error triggered? (in this example, DuplicateKeyValue) >> Unfortunately, no. What is needed is an INSERT BUT UPDATE IF DUPLICATE type of SQL construct added to the basic INSERT statement. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Mar 23 2006 4:29 PM | Permanent Link |
"Scott Martin" | That is what I read somewhere, but was not sure.
Can I make a request for this? huh, huh, please, please .... seems to be a useful option. Regards, Scott. "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:794B71F6-2B9D-422D-B221-CEBE9D076426@news.elevatesoft.com... > Scott, > > << Is there a method to determine the value of the duplicate key when the error triggered? (in this example, > DuplicateKeyValue) >> > > Unfortunately, no. What is needed is an INSERT BUT UPDATE IF DUPLICATE type of SQL construct added to the basic > INSERT statement. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > > |
Fri, Mar 24 2006 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Scott
Shouldn't you check to see if it exists BEFORE trying to insert it? Roy Lambert |
Fri, Mar 24 2006 5:26 AM | Permanent Link |
Chris Erdal | "Scott Martin" <scottmartin@pdq.net> wrote in
news:AA77D635-CFF0-42E2-BA23-4E59BAC787A6@news.elevatesoft.com: > with qryTemp do begin > if Active then Close; > SQL.Clear; > SQL.Text := 'INSERT INTO Inventory SELECT * FROM SWFY'; > try > ExecSQL; > Close; > except > on E: Exception do begin > // error 9729 duplicate key found , DBISAM_KEYVIOL > SQL.Clear; > SQL.Text := 'UPDATE Inventory SET Qty = 2 WHERE StockSerial = > '+QuotedStr(DuplicateKeyValue); try > ExecSQL; > Close; > except > end; > end; > end; > end; > > You could try something like: -------------------------------8<--------------------------- SELECT I.StockSerial aSerial, S.Qty + I.Qty aQty INTO memory\tmpTbl FROM Swfy S, Inventory I WHERE S.StockSerial = I.StockSerial ; UPDATE Inventory SET Qty = aQty FROM Inventory JOIN memory\tmpTbl ON StockSerial = aSerial ; DELETE FROM Swfy WHERE StockSerial IN (SELECT aSerial FROM memory\tmpTbl) ; DROP TABLE memory\tmpTbl ; INSERT INTO Inventory SELECT * FROM SWFY ; -------------------------------8<--------------------------- -- Chris |
Fri, Mar 24 2006 9:55 AM | Permanent Link |
"Scott Martin" | Roy,
Now there is a thought ....;p In the old version, using tables, I used a do while loop and findkey, worked as expected. The new version is all SQL based, so I was hoping for a DUPLICATE contruct as Tim mentioned so I could issue a basic SQL statement. I see chris has provided a workaround using a memory file. Regards, Scott. "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:DB90FA71-A698-4350-A8EA-305259ED7AD0@news.elevatesoft.com... > Scott > > > Shouldn't you check to see if it exists BEFORE trying to insert it? > > Roy Lambert > |
Fri, Mar 24 2006 9:56 AM | Permanent Link |
"Scott Martin" | Chris,
Thanks, I will give it a run. Regards, Scott. "Chris Erdal" <chris@No-Spam-erdal.net> wrote in message news:Xns979074143207614torcatis@64.65.248.118... > You could try something like: |
Fri, Mar 24 2006 11:38 AM | Permanent Link |
"Donat Hebert \(WSI\)" | Scott, why don't you write it all in SQL versus interating the dataset.
Insert all records that there is no match on primary key. (new Records) Then .. update the rest or update them all ... Insert into Target Select s.* from Source Left join target t on s.pk = t.pk where t.pk is null; Then run your update statement for all the record of delete the ones that you just insert from your source .. whatever makes sense .. Another option .. Donat. |
This web page was last updated on Thursday, May 23, 2024 at 07:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |