Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Is there a simple replacement for DMISAM3's "FROM" option in the UPDATE? |
Sun, Mar 2 2014 10:17 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi All
DBISAM v3 supported UPDATE statements like this :- UPDATE BankTrans B SET B.BatchNo = M.BatchNo FROM BankTrans B JOIN MEMORY "TEMP00001440" M ON M.TransNo = B.TransNo WHERE B.BatchNo = 6 I've successfully negotiated the route for most of my other MEMORY SQL and my code generated this:- UPDATE BankTrans B SET B.BatchNo = M.BatchNo FROM BankTrans B JOIN "MEMORYDB"."TEMP00001440" M ON M.TransNo = B.TransNo WHERE B.BatchNo = 6 BUT I get an error message:- --------------------------- Debugger Exception Notification --------------------------- Project apm.exe raised exception class EEDBException with message 'ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Expected end of expression but instead found FROM)'. .... and see in the ElevateDB manual that FROM doesn't exist Obviously I could use navigational methods to run down my temporary memory table and update the BankTrans table. In this case there the volumes are low, but in general I'd like to chuck the SQL at the server and let it do its stuff and not overload the network. Is there a simple replacement for the "FROM" option in the UPDATE? Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Sun, Mar 2 2014 10:32 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi again!
I meant DBISAM3 of course - saw that as soon as I pressed <Send> (I need my wife to proof read stuff obviously!) I have just found this in the migration manual:- "The FROM clause is no longer supported. ElevateDB can use correlated sub-queries in the UPDATE values and/or WHERE clause." So have come up with my first "correlated sub-query" (I think) and this seems like it should work:- UPDATE BankTrans SET BatchNo = 6 WHERE TransNo IN (SELECT TransNo FROM "MEMORYDB"."TEMP00001440") Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Tue, Mar 11 2014 7:53 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
<< So have come up with my first "correlated sub-query" (I think) and this seems like it should work:- >> Yes, that will work just fine. You can also use a correlated sub-query: UPDATE BankTrans BT SET BatchNo = 6 WHERE EXISTS(SELECT TransNo FROM "MEMORYDB"."TEMP00001440" T WHERE T.TransNo = BT.TransNo) Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 11 2014 6:47 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Thanks Tim
Am I right in thinking that your code will run a bit/lot faster than mine? Mind you I doubt that there will ever be more than a few hundred records involved - usually < 100. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:807054D0-428F-4E28-A3CD-6F8255F4814D@news.elevatesoft.com... > Jeff, > > << So have come up with my first "correlated sub-query" (I think) and > this seems like it should work:- >> > > Yes, that will work just fine. > > You can also use a correlated sub-query: > > UPDATE BankTrans BT > SET BatchNo = 6 > WHERE EXISTS(SELECT TransNo FROM "MEMORYDB"."TEMP00001440" T WHERE > T.TransNo = BT.TransNo) > > Tim Young > Elevate Software > www.elevatesoft.com |
Wed, Mar 19 2014 10:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
<< Am I right in thinking that your code will run a bit/lot faster than mine? Mind you I doubt that there will ever be more than a few hundred records involved - usually < 100. >> It really depends upon the situation, but my version will consistently deliver decent performance. My version runs more like a join, and is a lot more flexible when you need to add more conditions. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |