UPDATE Purchases P SET P.Tag = TRUE
FROM Purchases P INNER JOIN PurchaseItems I ON I.InvoiceID = P.ID
WHERE UPPER(I.LedgerType) LIKE '%LOAN%' ;

Please advise

Andrew


I just tried it here and got the error message

ElevateDB Error #700 An error was found in the statement at line 2 and column 1 (Expected end of expression but instead found FROM)

which I would expect since UPDATEs & DELETEs in ElevateDB don't use JOIN but subselects

I've just had a look at the ElevateDB help and I can't see a reference to using JOIN. Which manual are you using?

Roy Lambert

Andrew


You can try this

UPDATE Purchases P SET P.Tag = TRUE
WHERE 'LOAN' IN (SELECT UPPER(LedgerType) FROM PurchaseItems I WHERE I.InvoiceID = P.ID)

Roy Lambert

Thanks Roy, my rookie mistake was when I looked up the Internet on ElevateDB "UPDATE JOIN" it returned:-

"UPDATE orders SET ShipToContact=Customer.Contact
FROM orders LEFT OUTER JOIN customer
ON customer.custno=orders.custno"

A closer look shows me this was for DBISAM - my error.