Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 18 total |
Update with inner join |
Mon, Oct 12 2020 2:44 PM | Permanent Link |
Alex | 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 |
Mon, Oct 12 2020 3:12 PM | Permanent Link |
Adam Brett Orixa Systems | Alex
UPDATES can only reference a single table. You need to use a WHERE: Something like this (it is untested!): UPDATE Pecuniam P SET Pecuniam.Euro = (SELECT Dare FROM OpLedger O WHERE O.ClosingID = P.ClosingID) FROM Pecuniam WHERE ClosingID IN (SELECT ClosingID FROM OpLedger WHERE Verified = True) |
Mon, Oct 12 2020 5:41 PM | Permanent Link |
Alex | Adam Brett wrote:
Alex UPDATES can only reference a single table. You need to use a WHERE: Something like this (it is untested!): UPDATE Pecuniam P SET Pecuniam.Euro = (SELECT Dare FROM OpLedger O WHERE O.ClosingID = P.ClosingID) FROM Pecuniam WHERE ClosingID IN (SELECT ClosingID FROM OpLedger WHERE Verified = True) Thank you Adam I tried it but got exactly the same error message |
Mon, Oct 12 2020 5:51 PM | Permanent Link |
Alex | I've tried the following variation
UPDATE P SET P.Euro = O.DareSum FROM Pecuniam P INNER JOIN (select ClosingID, sum(Dare) as DareSum from OpLedger group by ClosingID) as O on O.ClosingID = P.ClosingID always with the same error 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) |
Tue, Oct 13 2020 2:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Alex
Try UPDATE Pecuniam SET Pecuniam.Euro = (SELECT OpLedger.Dare FROM OpLedger WHERE Pecuniam.ClosingID = OpLedger.CosingID AND OpLedger.Verified = True) FROM Pecuniam Its one of the "fun" changes in the sql specification from DBISAM to ElevateDB - JOIN is only useful for SELECT statements for DELETE and UPDATE you have to use subselects One further point is that if the subselect produces multiple rows you'll have to use something like RANGE 1 TO 1 to reduce it to a single row. Roy Lambert |
Tue, Oct 13 2020 6:31 AM | Permanent Link |
Alex | Roy
Tried it but getting again the same error message: ElevateDB Error #700 An error was found in the statement at line 3 and column 2 (Expected end of expression but instead found FROM) |
Tue, Oct 13 2020 6:40 AM | Permanent Link |
Alex | Alex wrote:
Roy Ok, found the issue. The last FROM statement has to be deleted. Now it works. Thank you |
Tue, Oct 13 2020 8:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Alex
Sorry - missed that - needed more coffee Roy Lambert |
Tue, Oct 13 2020 12:00 PM | Permanent Link |
Alex | Roy
Actually it seemed to work but it takes forever (OpLedger about 100'000 rows, Pecuniam about 600 rows) but only a few rows need to be changed (about 50) I tried the following as well with the same result, it takes forever. UPDATE Pecuniam SET Euro = Euro - ( SELECT SUM(Dare) FROM OpLedger o JOIN Pecuniam p ON p.ClosingID = o.ClosingID WHERE o.Verified = true ) What am I doing wrong? |
Tue, Oct 13 2020 12:30 PM | Permanent Link |
Alex | Tried the following as well, same result. Takes forever
UPDATE Pecuniam SET Euro = Euro - ( SELECT SUM(Dare) FROM OpLedger WHEREOpLedger.ClosingID = Pecuniam.ClosingID AND OpLedger.Verified = True GROUP BY OpLedger.ClosingID ) It drives me insane |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |