Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 8 of 8 total |
Update one a RAM table from another table. |
Sat, Feb 11 2012 12:54 PM | Permanent Link |
Abdulaziz Al-Jasser | This may be easy (becuase I know how to do it in DBISM3), but how to do that in EDB2?
Regards, Abdulaziz Jasser |
Sun, Feb 12 2012 7:50 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Abdulaziz
Depends on exactly what you want to do. For simple updating then 1. set the TEDBQuery to the destination database (ie whatever you've called your in-memory database) 2. preface the table name in the SELECT FROM that you use to get the data with the database name Apart from the above its just like updating any table from any table. The SELECT INTO syntax is gone (and sadly missed) but if you look in the extensions ng you should find a script (the subject is "Create a memory table") to help with this. Roy Lambert [Team Elevate] |
Sun, Feb 12 2012 11:15 AM | Permanent Link |
Abdulaziz Al-Jasser | Thanks Roy for replying but maybe I should be more specific. What is wrong with the bellow clause?
UPDATE RAM.tbData D SET D.ItemNo = (SELECT I.ItemNo FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND I.YearSysNo = 4 AND I.ItemSysNo = D.ItemSysNo), D.ItemName_A = (SELECT I.ItemName_A FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND I.YearSysNo = 4 AND I.ItemSysNo = D.ItemSysNo), D.ItemName_E = (SELECT I.ItemName_E FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND I.YearSysNo = 4 AND I.ItemSysNo = D.ItemSysNo), D.UnitBarcodeNo = (SELECT I.UnitBarcodeNo FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND I.YearSysNo = 4 AND I.ItemSysNo = D.ItemSysNo) Regards, Abdulaziz Jasser |
Mon, Feb 13 2012 4:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Abdulaziz
I have no idea What error do you get? Which database is the query component pointing to? Do your subselects return a single row or multiple rows? Roy Lambert [Team Elevate] |
Mon, Feb 13 2012 5:32 AM | Permanent Link |
Abdulaziz Al-Jasser | Basically I am trying to update some fields in a table in RAM database from another table in another NONE-RAM (Called "MyDatabase") database. What kills me is that when I run the same SQL statement from the EDBManager it runs ok but from inside Delphi I get the bellow error:
ElevateDB Error #1011 An error occurred with the query (SELECT ALL "I"."ItemNo" AS "ItemNo" FROM "MyDatabase"."TB_Items" AS "I" WHERE "I"."BranchSysNo" = 1 AND "I"."YearSysNo" = 4 AND "I"."ItemSysNo" = "R"."ItemSysNo") (A scalar query can only return a single value) NOTES: In the original SQL statment there is NO "ALL" clause the in it, but the error message added the "ALL" clause to it. Any help? Regards, Abdulaziz Jasser |
Mon, Feb 13 2012 7:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Abdulaziz
>Basically I am trying to update some fields in a table in RAM database from another table in another NONE-RAM (Called "MyDatabase") database. What kills me is that when I run the same SQL statement from the EDBManager it runs ok but from inside Delphi I get the bellow error: What this generally means is that there is some difference between the database(s) you're accessing in EDBManager and inside your program. Remember ElevateDB is simply a Delphi application developed by Tim. There is no magic code inside. >ElevateDB Error #1011 An error occurred with the query (SELECT ALL "I"."ItemNo" AS "ItemNo" FROM "MyDatabase"."TB_Items" AS "I" WHERE "I"."BranchSysNo" = 1 AND "I"."YearSysNo" = 4 AND "I"."ItemSysNo" = "R"."ItemSysNo") (A scalar query can only return a single value) This is the important bit. What it means is that that WHERE clause returns more than one row (another indicator that the two databases are different). >NOTES: In the original SQL statment there is NO "ALL" clause the in it, but the error message added the "ALL" clause to it. Any help? You can ignore the ALL - its something Tim stuffs in. He can probably explain it but I can't, I just know it can be saely ignored. Roy Lambert [Team Elevate] |
Mon, Feb 13 2012 9:04 AM | Permanent Link |
Abdulaziz Al-Jasser | Thanks Roy,
It was a database differences. The application was querying a database that returns more than one record. I made some changes to it to force it to return one record. Thanks again for your reply. Regards, Abdulaziz Jasser |
Tue, Feb 21 2012 6:58 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Abdulaziz,
You should consider using this syntax instead (apart from your issue with multiple rows being returned): UPDATE RAM.tbData D SET (D.ItemNo, D.ItemName_A, D.ItemName_E, D.UnitBarcodeNo) = (SELECT I.ItemNo, I.ItemName_A, I.ItemName_E, I.UnitBarcodeNo FROM MyDatabase.TB_Items I WHERE I.BranchSysNo = 1 AND I.YearSysNo = 4 AND I.ItemSysNo = D.ItemSysNo) This "row value constructor" syntax will be much faster than doing the columns individually: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Row_Value_Constructors -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |