db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Inserting and deleting records
Date Sat, 31 Jan 2015 13:46:22 GMT

> On 31. jan. 2015, at 13.50, Bob M <rgmatthews@orcon.net.nz> wrote:
> Hi Dyre
> Thank you for your explanation
> I understand completely
> My concept of requiring the table to look exactly as I think it should look
> before any ORDER BY is WRONG!!!!!!!!!!!!!!!

Well, its an intuitive way to think about it. That is why some dbs have ROWID.
It is easy to think about a table as a vector, but it is really more like a hashtable, where
there is no implied order and you need to a key to access the record you’re interested in.

> I do, in fact, retrieve x records using ORDER BY exactly as you say to
> always get the x latest records.
> Now one last question, if I may.................
> After adding a new record, I should like to set the pointer to the
> penultimate latest record ordered by DATE and Time so that I can update the
> Profit field
> Exactly what code do I need to do that, please

I will not say that it is impossible, but probably hard. As you probably know, there isn’t
really a concept of pointer - the closest thing is a SCROLLABLE UPDATABLE result set. Unfortunately,
I don’t think Derby supports ORDER BY with scrollable result sets (I may be wrong, check
the docs), so that probably won’t help much…

But perhaps there is a simpler way to do what you’re attempting? So you insert a record
for a trade, and then after a while (when the trade has gone through?) you calculate the loss/profit
and update the record? Assuming this is the situation, sort of, here is what I would consider:

1) Leave a “not calculated yet” value (NULL comes to mind) in the profit column. Then
issue an UPDATE … WHERE PROFIT IS NULL. No need to know where the record is, as long as
you update the right one… will obviously only work as long as there is only one record with
NULL profit at a time...

2) Maybe there is an ID/TRADE NUMBER here? Presumably you could track (in your app) the ID
of the trade(s) which has/have not been finalized? Then you could do someting like UPDATE
… WHERE ID = <last trade id> 

3) A separate table for the unfinished trades. Update when you know the profit, and copy to
the real table…



View raw message