Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Message-ID: <5266740E.7050207@oracle.com>
Date: Tue, 22 Oct 2013 05:48:14 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: Retrieving the 'oldest' record and deleting it
References: <1382381612721-134915.post@n7.nabble.com>
In-Reply-To: <1382381612721-134915.post@n7.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/21/13 11:53 AM, Bob M wrote:
> Hi
>
> I have the following code which retrieves the 'oldest' record and prints out
> some data
>
> rs = s.executeQuery("SELECT from tablename ORDER BY Date ASC, Time, ASC
> FETCH FIRST ROW ONLY");
> rs.next();
> String Date = rs.getString("Date");
> String Date = rs.getString("Time");
> myConsole.getOut().println("Date/Time " + Date ", " + Time);
>
> Now I wish to delete this record and the code is..........
>
> rs = s.executeQuery("DELETE from tablename WHERE ????");
>
> What is the ?????
>
> Bob M
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Retrieving-the-oldest-record-and-deleting-it-tp134915.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Hi Bob,

The following program may shed some light on how to perform positioned 
deletes.

Hope this helps,
-Rick

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

import java.sql.*;

public class w
{
     public  static  void    main( String... args ) throws Exception
     {
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );

         conn.prepareStatement( "create table t( a int generated always 
as identity, b int )" ).execute();
         conn.prepareStatement( "insert into t( b ) values ( 10 ), ( 20 
), ( 30 )" ).execute();

         conn.setAutoCommit( false );

         Statement   cursorStatement = conn.createStatement();
         cursorStatement.setCursorName( "MYCURSOR" );
         ResultSet   rs = cursorStatement.executeQuery( "select * from t 
where b = 20 for update" );
         rs.next();

         conn.prepareStatement( "delete from t where current of 
MYCURSOR" ).executeUpdate();
     }

}