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

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)

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

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)

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

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)

Alex wrote:

Roy

Ok, found the issue. The last FROM statement has to be deleted. Now it works. Thank you

Alex


Sorry - missed that - needed more coffee

Roy Lambert

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?

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