Take a look at this example for deleting oldest record (adjust and test
it!). I'm not sure if there's a cleaner way of doing this that Derby is
capable of, probably someone on this list will point it out.
DELETE FROM table1
WHERE CAST (due_date || ' ' || due_time AS TIMESTAMP) = (SELECT MAX(CAST
(due_date || ' ' || due_time AS TIMESTAMP)) from table1)
/--Regards, Alex/
------------------------------------------------------------------------
*From:* Bob M <rgmatth...@orcon.net.nz>
*Sent:* Saturday, January 31, 2015 10:27AM
*To:* Derby Discussion
*Subject:* Inserting and deleting records
Hi
I have a section of code which I hope does the following:-
1) inserts one new record with the latest (date + time)
2) deletes one old record with the oldest (date + time)
When I run the code for example, I get 194 new records written where I
expect them to be at the end of the table BUT 10 new records are written at
the beginning of the table(out of date+time) sequence
What should I do to rectify this situation so that all 204 new records
appear at the end of the table ?
The code I have is as follows:-
*******************************************
psInsert = conn.prepareStatement("INSERT INTO TABLE VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
statements.add(psInsert);
27 - psInsert.xxx lines
psInsert.executeUpdate();
// retrieve oldest record from the table
rs = s.executeQuery("SELECT * FROM TABLE ORDER BY Date ASC,"
+ " Time ASC FETCH FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
int Time2 = rs.getInt("Time");
// and now delete this record.............
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * from TABLE WHERE Date = '"
+ Date2 + "' AND Time = " + Time2
+ " FOR UPDATE");
rs.next();
conn.prepareStatement("DELETE FROM TABLE WHERE CURRENT OF
MYCURSOR").executeUpdate();
// commit the above transactions
conn.commit();
} // end of adding new record and deleting oldest one
****************************************************
Bob M
--
View this message in context:
http://apache-database.10148.n7.nabble.com/Inserting-and-deleting-records-tp143723.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
