Hi I am trying to code for the ability to update either two records (the two records which have the latest date & time) or just one (the record which has the latest date & time) for the profit field If two_time is TRUE, then two records are requiring updating, otherwise just the one record The records which need updating have 999.99 in the profit field initially There are x+2 records in the database At the moment, no updating is taking place - any idea why ?
**************************************************************** // if we have two records to update // i.e. the penultimate record which was closed at the last clock-in point // and the ultimate record which was opened at the same time and has since closed if ((two_time) && (!initialize) && (myCanTrade) && (myCurrPositionValue >0)) { // retrieve the last two records rs = s.executeQuery("SELECT * FROM TABLE ORDER BY Trading_Date ASC," + " Trading_Time ASC OFFSET x ROWS FETCH NEXT 2 ROWS ONLY"); rs.next(); String Date2 = rs.getString("Trading_Date"); int Time2 = rs.getInt("Trading_Time"); s.setCursorName("MYCURSOR"); rs = s.executeQuery("SELECT * FROM TABLE WHERE Date = " + Date2 + " AND Time = " + Time2 + " FOR UPDATE"); rs.next(); conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS = prev_profit WHERE CURRENT OF MYCURSOR").executeUpdate(); prev_profit = 0; //move on to the last record rs.next(); String Date3 = rs.getString("Trading_Date"); int Time3 = rs.getInt("Trading_Time"); s.setCursorName("MYCURSOR"); rs = s.executeQuery("SELECT * FROM TABLE WHERE Date = " + Date3 + " AND Time = " + Time3 + " FOR UPDATE"); rs.next(); conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS = profit WHERE CURRENT OF MYCURSOR").executeUpdate(); two_time =false; } // end of updating the last two records else { // else, if we have just the one record to update // retrieve and output date and time of the latest record from the table // providing this is not the first trade and we are about to open a new trade ! if ((!initialize) && (myCanTrade) && (myCurrPositionValue >0)) { psUpdate = conn.prepareStatement("UPDATE TABLE SET PROFIT_LOSS=? WHERE PROFIT_LOSS = 999.99"); statements.add(psUpdate); psUpdate.setDouble(1, profit); psUpdate.executeUpdate(); } } // end of updating latest record ************************************************************* Bob M -- View this message in context: http://apache-database.10148.n7.nabble.com/updating-a-maximum-of-2-records-tp143764.html Sent from the Apache Derby Users mailing list archive at Nabble.com.