CODEWITHSUNDEEP
sqlite
KK99
KK99

Reputation: 1989

Insert or update in SQLite

I have a simple table with 4 columns (ID (auto gen), ip,date,visits)

ex:

1,'10.10.0.10', '2017-03-01',1

I am looking for Insert or Update in SQLite which needs to check the ip & date column and if exists update visits +1 else insert a new row.

i can issue 2 sql but wanted to keep it in 1 sql.

Is this possible in SQLite?

Upvotes: 0

Views: 4494

Answers (1)

Murphy
Murphy

Reputation: 3999

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;

Upvotes: 2

Related Questions