On 10/22/13 8:44 PM, Bob M wrote:
Hi there
The error is that my SELECT can not have "For UPDATE" ????
My current code is
***************************
// retrieve and output date and time of oldest record from the table
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY");
rs.next();
String Date2 = rs.getString("Date");
String Time2 = rs.getString("Time");
myConsole.getOut().println("Date/Time: " + Date2 + ", " + Time2);
// and now delete this record.............
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT * FROM tablename ORDER BY Date ASC, Time ASC
FETCH FIRST ROW ONLY FOR UPDATE");
rs.next();
psDelete = conn.prepareStatement("DELETE FROM tablename WHERE CURRENT OF
MYCURSOR");
statements.add(psDelete);
myConsole.getOut().println("Deleted oldest record");
--
View this message in context:
http://apache-database.10148.n7.nabble.com/deleting-a-record-still-a-problem-tp134975.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Hi Bob,
Another way to solve this problem would be to add a unique key column to
your table and use that to specify which row should be deleted. The
following script shows how this would work.
Hope this helps,
-Rick
-----------------
connect 'jdbc:derby:memory:db;create=true';
-- create a little procedure to pause between inserts
create procedure sleep( millis bigint )
language java parameter style java no sql
external name 'java.lang.Thread.sleep';
create table t( id int generated always as identity, dateCol date,
timeCol time );
insert into t( dateCol, timeCol ) values ( current date, current time );
call sleep( 1000 );
insert into t( dateCol, timeCol ) values ( current date, current time );
call sleep( 1000 );
insert into t( dateCol, timeCol ) values ( current date, current time );
select * from t order by dateCol, timeCol;
select id from t order by dateCol, timeCol fetch first 1 row only;
delete from t where id =
( select id from t order by dateCol, timeCol fetch first 1 row only );
select * from t order by dateCol, timeCol;
