Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By JosAH

Thread: Problem with ResultSet method deleteRow()

  1. #1
    dazey is offline Member
    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Default Problem with ResultSet method deleteRow()

    Following is a code i am working on :-
    Java Code:
    import java.sql.*;
     
     
    public class deleteRowDemo
    {
        public static void main(String args[])
        {
        Connection con;
        Statement stmt;
        ResultSet rs;
        PreparedStatement ps;
         
         try
           {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con=DriverManager.getConnection("jdbc:odbc:Project");
          stmt=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
          rs=stmt.executeQuery("select Mword, from Model,Stud where Mword=Sword");
           
           while(rs.next())
           {
           ps=con.prepareStatement("insert into Compare (Cword) values (?)");
           ps.setString(1,rs.getString(1));
           ps.executeUpdate();  
           rs.deleteRow();
           }
          con.close();
        }
    catch(Exception e)
    {
        System.out.println(e);
         
    }}
    }
    There are two tables Model and Stud. The two tables are beng compared and all the words that are similar in both the tables are inserted in another table called Compare, at the same time i wish to delete the words from both the Stud and Model tables. The function deleteRow() works fine when the select statement has only one table , but how to work it out for complex select statements with multiple tables.
    PLEASE HELP!!

  2. #2
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,445
    Blog Entries
    7
    Rep Power
    20

    Default Re: Problem with ResultSet method deleteRow()

    You have to create another (or two other) SQL statement(s) for it. I think it can be done by one SQL statement, but my SQL-knowledge is rusty and fragmented; it needs a nested select-clause ... I'd do it with two separate SQL statements (one for each table).

    kind regards,

    Jos
    dazey likes this.
    cenosillicaphobia: the fear for an empty beer glass

  3. #3
    dazey is offline Member
    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Default Re: Problem with ResultSet method deleteRow()

    Thank you so much for the help...
    I tried the following Nested Select statement like you suggested

    rs=stmt.executeQuery("select Mword from Model where Mword in (select Sword from Stud )");

    It worked half way, :( the row from the Model Table was deleted but the Stud table was left unaltered,,i think that's because the outer select statement has Mword as the only attribute,.
    If you have any idea about how to delete rows from both the tables, please do reply..
    Last edited by dazey; 05-05-2013 at 03:07 PM.

  4. #4
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,445
    Blog Entries
    7
    Rep Power
    20

    Default Re: Problem with ResultSet method deleteRow()

    Quote Originally Posted by dazey View Post
    Thank you so much for the help...
    I tried the following Nested Select statement like you suggested

    rs=stmt.executeQuery("select Mword from Model where Mword in (select Sword from Stud )");

    It worked half way, :( the row from the Model Table was deleted but the Stud table was left unaltered,,i think that's because the outer select statement has Mword as the only attribute,.
    If you have any idea about how to delete rows from both the tables, please do reply..
    I gave it a few more thoughts and I now think you have to use two SQL queries for it: delete from X where X.column = ? and delete from Y where Y.column = ? where the ? gets the value of a field that is present in both tables X and Y (and is present in the current row of your result set).

    kind regards,

    Jos
    cenosillicaphobia: the fear for an empty beer glass

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: Problem with ResultSet method deleteRow()

    Does Access have cascade on delete?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. ResultSet Function deleteRow() : PLEASE HELP!!
    By dazey in forum Advanced Java
    Replies: 1
    Last Post: 05-05-2013, 02:59 PM
  2. Problem with ResultSet when using GUI
    By RefugeX in forum New To Java
    Replies: 6
    Last Post: 12-01-2011, 10:21 PM
  3. Resultset comparision method
    By EADS in forum Advanced Java
    Replies: 0
    Last Post: 07-27-2011, 11:51 AM
  4. Problem with ResultSet
    By flaquitqm in forum JDBC
    Replies: 8
    Last Post: 01-27-2010, 08:49 AM
  5. problem with ResultSet.updateRow()
    By kvikas in forum New To Java
    Replies: 0
    Last Post: 04-14-2008, 10:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •