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 17:41:19 GMT

> On 31. jan. 2015, at 14.59, Bob M <rgmatthews@orcon.net.nz> wrote:
> 
> Thank you Dyre for a brilliant reply..................
> I do have a TRADE_ID number
> Also, I only ever have ONE open trade at any point in time
> I follow your three points completely
> 
> Because I already have code which retrieves x records ordered by date and
> time I am considering the following code to answer my own question
> *************************************************
> // retrieve and update date and time of penultimate record
> // I retrieve the latest 2 records
> rs = s.executeQuery("SELECT * FROM TABLE ORDER BY Date ASC,"
>        + " Time ASC OFFSET x ROWS FETCH NEXT 2 ROWS ONLY");
> myConsole.getOut().println("Successfully retrieved latest 2 records from
> TABLE: ");
> rs.next();
> String Date3 = rs.getString("Date");
> int Time3 = rs.getInt("Time");
> s.setCursorName("MYCURSOR");
> rs = s.executeQuery("SELECT * from TABLE WHERE Date = '"
>        + Date3 + "' AND Time = " + Time3 
> + " FOR UPDATE");
> rs.next();
> conn.prepareStatement("UPDATE FROM TABLE SET PROFIT_LOSS = profit WHERE
> CURRENT OF MYCURSOR").executeUpdate();
> 
> myConsole.getOut().println("Updated penultimate record");
> *******************************************

Clever! I have not used scrollable updatable results much, but I believe you don’t need
the last update statement - you should be able to issue the rs.setInt() (or what is appropriate)
to update the proft direcly. The only sticking point is that I don’t know if you’re guaranteed
that the two records are in a particular order (since there cannot be an ORDER BY). You may
have to check the which of the two records you’re at and possibly move…

Best of luck! 

Dyre 
> 
> Bob M
> 
> 
> 
> 
> 
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723p143729.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message