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?

MySQL or SQLite? They are different DMS and the solution can be different. — Pred, Sep 30 '15 at 08:59
See this question: http://stackoverflow.com/questions/2717590/sqlite-upsert-on-duplicate-key-update — Pred, Sep 30 '15 at 09:20
@Pred: Thanks for spotting this. I hadn't even noticed the SQLite tag and answered for MySQL accordingly. I've updated my answer now to show how the same thing is done in SQLite. — Thorsten Kettner, Sep 30 '15 at 09:48

Thorsten Kettner · Accepted Answer · 2015-09-30T09:40:55.843

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);

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

1 Answers1