sqlite insert 1 row every day is same date

Question

I want to insert a row (_id,rank,date) in sqlite through android. But i want the row to be only one per day. So if i select another rank today it will overwrite the row, so that only the last row is kept every day

example i insert

(rank,date) -> (3,20140322)
(rank,date) -> (5,20140322)
(rank,date) - (2,20140322)
(rank,date) - (3,20140323)
(rank,date) - (6,20140323)

i want in the sqlite to be only

(rank,date) - (2,20140322)
(rank,date) - (6,20140323)

how can i define the date column? and how i can proceed to obtain to have only the last value every day?

Answer 1

Add a UNIQUE constraint to your date column, for example:

CREATE TABLE tablename(...
   date TEXT UNIQUE, ...)

Then use INSERT OR REPLACE to do the inserts, e.g.

INSERT OR REPLACE INTO tablename(...,date) VALUES(..., 'todaysdate')

or using ContentValues with Android's convenience methods:

db.insertWithOnConflict(table, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

When the UNIQUE constraint (or any other constraint for that matter) is violated, the old violating rows are first removed and then the new row is inserted.