How to limit a table row count and replace if found?

Question

I'm fairly new to the database queries and such and right away I find my self with a complex situation.

My DB table contains 3 columns:

_id | stringValue | timeStamp

I have 3 constraints;

1. Max rows allowed equals 100.
2. If table is full the oldest item will be removed.
3. If a new item to be added matches the stringValue than only the timestamp will be updated.

I'm currently making 3 queries:

1. Check if item already exists if it does just update it. If it doesn't just add it.
2. If item did not exist check how many items are on the table
3. If over 100 items remove the oldest

I'm thinking there might be a one query I can do to achieve all this... Any tips or ideas would be appreciated!

Answer 1

For the issue of insert if an item doesn't existing or updating if it does not, use "INSERT OR REPLACE INTO" for your sql statement.

For the constraint on the max number of rows, this is something you'll have to handle separately and there are a few ways you can do this.

Here's an older post to check out: Limit an sqlite Table's Maximum Number of Rows