Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 18 of 18 total |
Update with inner join |
Wed, Oct 14 2020 3:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Alex
Normally I'd suggest adding a WHERE clause for Pecuniam. but I'm not sure what may be appropriate. As a first step split out the sub-select and run as a separate query, enable production of the execution plan and see what it says. My bet would be that you're missing indices. If the data isn't confidential or to large post the database to the binaries and I'll have a look. If it is to large then is there a site I can download from? Roy Lambert |
Wed, Oct 14 2020 6:17 AM | Permanent Link |
Alex | Roy
Thank you for your help. The whole database is around 178 MB. I could send it with Wetransfer . I would need an email to send you the link. Alex |
Wed, Oct 14 2020 6:25 AM | Permanent Link |
Alex | Roy
You were right. It was an indexing issue. The field ClosingID wasn't indexed. Now it works just wonderful. Thanks again for the pressure relief |
Wed, Oct 14 2020 6:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Alex
No problem. I know how I felt when after beating myself up to learn DBISAM's SQL I then switched to ElevateDB and found things were different The good news part is you now have an idea on how to address problems - break the sql down to the smallest parts and run and produce an execution plan - it is a brilliant utility. Roy Lambert |
Wed, Oct 14 2020 6:57 AM | Permanent Link |
Alex | Roy
That is what I did, tried the single parts by themselves and they were ok. Now I tried it on the whole table and the problem is that now it calculates perfectly the single row but sets the Euro field of all the remaining rows to NULL. |
Wed, Oct 14 2020 10:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Alex
send the database, config & catalog files to roy at lybster dot me dot uk and I'll have a look Roy Lambert |
Wed, Oct 14 2020 11:35 AM | Permanent Link |
Yusuf Zorlu MicrotronX - Speditionssoftware vom Profi | Alex wrote:
> Hello > > I am trying to make the following sql work : > > UPDATE Pecuniam > SET Pecuniam.Euro = OpLedger.Dare > FROM Pecuniam > INNER JOIN OpLedger > ON Pecuniam.ClosingID = OpLedger.CosingID > WHERE OpLedger.Verified = True > > but get the following error message: > ElevateDB Error #700 An error was found in the statement at line 3 > and column 3 (Expected end of expression but instead found FROM) > > What is wrong? > > Thank you ElevateDB is not capable of doing a join with a Update-statement. Would be wonderfull if but at the moment you have to use something like this: Update Pecuniam set Pecuniam.Euro = (select OpLedger.Dare from OpLedger where OpLedger.CosingID=Pecuniam.ClosingId) where Pecuniam.ClosingId in (select OpLedger.CosingId from OpLedger where OpLedger.Verified=true) -- -- Yusuf Zorlu | MicrotronX |
Wed, Oct 14 2020 6:11 PM | Permanent Link |
Alex | Yusuf
Thank you a lot Yusuf, that nailed it. |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |