Insert or update in SQLite

If you generate a uniqe index for IP and date you can use the insert or replace statement to achieve this in two steps:

1. Try to query the current number of visits for IP and date; set to 0 if record not found.

2. Execute the insert or replace for IP and date (I'm using named parameters in this example; you need to bind them to the appropriate values):

   insert or replace into Visits (id, ip, date, visits)
   values (NULL, :ip, :date, :visits + 1);
   

You can add a table contstraint to create the unique index like in the following example, or you use a separate create unique index statement:

create table Visits (..., unique (ip, date));

Addendum: There's even a possibility to update the number of visits in one query:

insert or replace into Visits (id, ip, date, visits)
    select NULL, :ip, :date, ifnull(max(visits), 0) + 1
    from Visits
    where ip = :ip and date = :date;