![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Fri, Nov 9 2018 1:55 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Hi, is it possible with ElevateDB to insert or update as it is possible with Advantage like this:
MERGE INTO mytable using (select * from testtable where testfield='ABC') u1 on u1.testfield=mytable.testfield WHEN NOT MATCHED THEN insert (testfield, otherfield) value s('ABC', 'DDDD') WHEN MATCHED THEN update set otherfield='DDDD' Yusuf Zorlu MicrotronX |
Fri, Nov 9 2018 2:40 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Yusuf
No. What you've posted, to one not familiar with Advantage, seems to be a mixture of INSERT and UPDATE. The nearest you could get would be a script which would run an INSERT then an UPDATE. Roy Lambert |
Fri, Nov 9 2018 2:54 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Roy Lambert wrote:
No. What you've posted, to one not familiar with Advantage, seems to be a mixture of INSERT and UPDATE. The nearest you could get would be a script which would run an INSERT then an UPDATE. Hi Roy, this script checks if there is already a record with "ABC" in the table, if a row is found, it updates it and if there is no row, it insert's a new record. We can do this in delphi with two scripts but i wanted to know if there is already similar functionality available in elevatedb Yusuf Zorlu MicrotronX |
Fri, Nov 9 2018 9:41 AM | Permanent Link |
Adam Brett Orixa Systems | Yusuf
I think you should spend a bit of time looking at Stored Procedures in EDB. These allow you to perform a lot of complex functions that would otherwise have to be coded in Delphi. Stored Procedures have the concept of a CURSOR which can be opened using a STATEMENT. DECLARE Crsr CURSOR FOR Stmt; PREPARE Statement FROM ' SELECT * FROM MyTable WHERE Field = ''ABC'' '; OPEN Crsr; IF Crsr.ROWCOUNT . 0 THEN --DoSomething EXECUTE IMMEDIATE ' INSERT INTO MyTable ... '; ELSE --DoSomethingElse; EXECUTE IMMEDIATE ' UPDATE MyTable ... '; END IF; You would need to iterate through a dataset to make this work if you had multiple records (which would need a second CURSOR). Adam |
Fri, Nov 9 2018 10:04 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Yusuf
Pretty much anything you can do in DBIASM you can do in ElevateDB, sometimes there's different syntax. Sometimes you can do a lot more. DBISAM scripts are simple queries executed one after the other according to the delimiter. You can achieve the same in ElevateDB by using EXECUTE IMMEDIATE inside a script, or you can run two queries. Better you can write a script, or stored procedure using SQL/PSM (ElevateDB's programming language) to do essentially what you would do using table navigation methods in Delphi but as an SQL function. If you post the DBISAM code you have I'll try and turn it into ElevateDB for you. Roy Lambert |
Thu, Nov 15 2018 9:08 PM | Permanent Link |
Fernando Dias ![]() | Yusuf ,
We don't but MERGE has been in the "wish list" for some time. Let's hope that Tim can make it soon ![]() -- Fernando Dias [Team Elevate] |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |