db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex <alex3...@zoho.com>
Subject Re: Inserting and deleting records
Date Sat, 31 Jan 2015 08:59:06 GMT
...sorry, that's would delete newest record (recordS, if there are 
several with identical date/time). Use MIN instead of MAX for oldest.

/--Regards, Alex/

------------------------------------------------------------------------

*From:* Me
*Sent:* Saturday, January 31, 2015 11:55AM
*To:* Derby Discussion
*Subject:* Re: Inserting and deleting records
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 <rgmatthews@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.



Mime
View raw message