Hi All

DBISAM v3 supported UPDATE statements like this :-

UPDATE BankTrans B
SET B.BatchNo = M.BatchNo
FROM BankTrans B
JOIN MEMORY "TEMP00001440" M ON M.TransNo = B.TransNo
WHERE B.BatchNo = 6

I've successfully negotiated the route for most of my other MEMORY SQL and
my code generated this:-

UPDATE BankTrans B
SET B.BatchNo = M.BatchNo
FROM BankTrans B
JOIN "MEMORYDB"."TEMP00001440" M ON M.TransNo = B.TransNo
WHERE B.BatchNo = 6

BUT I get an error message:-

---------------------------
Debugger Exception Notification
---------------------------
Project apm.exe raised exception class EEDBException with 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)'.

.... and see in the ElevateDB manual that FROM doesn't exist

Obviously I could use navigational methods to run down my temporary memory
table and update the BankTrans table. In this case there the volumes are
low, but in general I'd like to chuck the SQL at the server and let it do
its stuff and not overload the network.

Is there a simple replacement for the "FROM" option in the UPDATE?

Cheers

Jeff





--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Hi again!

I meant DBISAM3 of course - saw that as soon as I pressed <Send>  (I need my
wife to proof read stuff obviously!)

I have just found this in the migration manual:-

"The FROM clause is no longer supported. ElevateDB can use
correlated sub-queries in the UPDATE values and/or WHERE
clause."

So have come up with my first "correlated sub-query"  (I think) and this
seems like it should work:-

UPDATE BankTrans
SET BatchNo = 6
WHERE TransNo IN (SELECT TransNo FROM "MEMORYDB"."TEMP00001440")

Cheers

Jeff


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff,

<< So have come up with my first "correlated sub-query"  (I think) and this
seems like it should work:- >>

Yes, that will work just fine.

You can also use a correlated sub-query:

UPDATE BankTrans BT
SET BatchNo = 6
WHERE EXISTS(SELECT TransNo FROM "MEMORYDB"."TEMP00001440" T WHERE
T.TransNo = BT.TransNo)

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim

Am I right in thinking that your code will run a bit/lot faster than mine?
Mind you I doubt that there will ever be more than a few hundred records
involved - usually < 100.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:807054D0-428F-4E28-A3CD-6F8255F4814D@news.elevatesoft.com...
> Jeff,
>
> << So have come up with my first "correlated sub-query"  (I think) and
> this seems like it should work:- >>
>
> Yes, that will work just fine.
>
> You can also use a correlated sub-query:
>
> UPDATE BankTrans BT
> SET BatchNo = 6
> WHERE EXISTS(SELECT TransNo FROM "MEMORYDB"."TEMP00001440" T WHERE
> T.TransNo = BT.TransNo)
>
> Tim Young
> Elevate Software
> www.elevatesoft.com

Jeff,

<< Am I right in thinking that your code will run a bit/lot faster than
mine? Mind you I doubt that there will ever be more than a few hundred
records involved - usually < 100. >>

It really depends upon the situation, but my version will consistently
deliver decent performance.  My version runs more like a join, and is a lot
more flexible when you need to add more conditions.

Tim Young
Elevate Software
www.elevatesoft.com