Insert the newset value in a table and drop the old ones

Question

I have a method which saves somes records for a machine in a table. However, I may insert by accident multiple times the records for the same machine in a day, so I want for this day to save the newest insert and drop the old ones. I thought one solution:

String sq = "SELECT date, idMachine "
          + "FROM MACHINES_RECORDS WHERE date = ? AND idMachine = ?";

 try {       
        Class.forName(typeDB);
        c = DriverManager.getConnection(path);            
        stm = c.prepareStatement(sq);  
        ResultSet rs = stm.executeQuery();

        if (rs.next()) {                
           do {
                 //call an another method to drop this value
               } while(rs.next());
         }else {
               //call an another method to insert this value
         }       
        }catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            if (stm != null)
                stm.close();
            if (c != null)
        c.close();
  }

Is there any better/smartest solution from this, so I can make all theses actions in the same method?

Answer 1

With a unique index (possibly the primary key) on date and idmachine you would use INSERT ... ON DUPLICATE KEY UPDATE, so as to insert when there is no entry for that machine on that day or update the existing record with the new data otherwise.

insert into machines_records
  (machineid, date, data)
values
  (@id, @date, @data)
on duplicate key update data = @data;

EDIT: I see you removed the MySQL tag. So you want an answer for SQLite. In SQLite you'd use INSERT OR REPLACE:

insert or replace into machines_records
  (machineid, date, data)
values
  (@id, @date, @data);