0

Here is my java code:

public int function(String name)
{
    int i=0;

    int id=0;


    try
    {
        String get_id="SELECT id FROM table_name JOIN tbl_2 ON tbl_name.pk_id = tbl_2.fk_name_id where active_flag=1 and updated_at <= NOW() - INTERVAL 3 MINUTE";

        Statement stmt=(Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery(get_id);

        while (rs.next())
          {
             id= rs.getInt("id");

             String update="UPDATE table_name SET active_flag=NULL WHERE id="+id+"";    
             stmt.executeUpdate(update);
          }


        i=1;
    }
    catch(Exception e)
    {
         System.out.println(e);
    }


    return i;

  }

while running this, I am getting SQL Exception:

java.sql.SQLException: Operation not allowed after ResultSet closed

Please help me to perform this operation in an alternative and simple way. I just want to update the same table with the value obtained from first SQL Query, as there is n number of values in table and the value change dynamically, I used while loop. please help me to fix this. Thanks in advance.

Thirupathi Sriramoji
  • 153
  • 1
  • 5
jasim
  • 459
  • 1
  • 6
  • 24
  • Why not you use single sql query `UPDATE table_name SET active_flag=NULL WHERE id IN (SELECT id FROM table_name JOIN tbl_2 ON tbl_name.pk_id = tbl_2.fk_name_id where active_flag=1 and updated_at <= NOW() - INTERVAL 3 MINUTE)`? – Wundwin Born Jul 04 '14 at 10:14
  • ERROR 1093 (HY000): You can't specify target table 'tbl_driver' for update in FROM clause – jasim Jul 04 '14 at 10:25
  • I can't see `tbl_driver` alias in your query or my example query. You may need to look into [this](http://stackoverflow.com/questions/13188002/error-1093-hy000-you-cant-specify-target-table-a-for-update-in-from-clause) – Wundwin Born Jul 04 '14 at 10:30
  • sorry i am using different table name here from my real table name. the rest are same – jasim Jul 04 '14 at 10:42
  • Other way you can do is get the data from resultset and store it a map and later iterate the map and update the table but the best way is subqueries – SparkOn Jul 04 '14 at 10:49
  • this is my actual code i just use now,: "UPDATE tbl_driver SET active_flag=NULL WHERE Pk_driver_id = (SELECT * FROM tbl_driver JOIN tbl_location ON tbl_driver.pk_driver_id = tbl_location.fk_driver_id WHERE updated_at <= NOW() - INTERVAL 3 MINUTE AND active_flag=1);" "ERROR 1241 (21000): Operand should contain 1 column(s)" how to overcome this error? please help – jasim Jul 04 '14 at 11:03

1 Answers1

0

Just rewrite the update sql statement to:

UPDATE table_name 
SET active_flag=NULL 
WHERE EXISTS
(
    SELECT 
        NULL 
    FROM 
        table_name as tbl
        JOIN tbl_2 
            ON tbl_name.pk_id = tbl_2.fk_name_id 
        where tbl.active_flag=1 
        and tbl.updated_at <= NOW() - INTERVAL 3 MINUTE
        AND tbl.id=table_name.id
)
Arion
  • 31,011
  • 10
  • 70
  • 88
  • i need to "UPDATE table_name SET active_flag=NULL" for columns with specific Id in table – jasim Jul 04 '14 at 10:29
  • ERROR 1093 (HY000): You can't specify target table 'tbl_driver' for update in FROM clause – jasim Jul 04 '14 at 10:40
  • tbl_driver is the actual table name that i use instead of table_name in my actual query – jasim Jul 04 '14 at 10:48