IF-Statement in SQLite: update or insert?

SQLite does not have an IF statement (see the list of supported queries)

Insetad, check out out ERIC B's suggestion on another thread. You're effectively looking at doing an UPSERT (UPdate if the record exists, INSERT if not). Eric B. has a good example of how to do this in SQLite syntax utilizing the "INSERT OR REPLACE" functionality in SQLite. Basically, you'd do something like:

INSERT OR REPLACE INTO Repetition (Word, Topic, Counts)    VALUES (  'behnam', 'mine',          coalesce((select Counts + 1 from Repetition                    where Word = 'behnam', AND Topic = 'mine)                   ,1)       )

Another approach is to INSERT ... SELECT ... WHERE ... EXISTS [or not] (SELECT ...);

I do this sort of thing all the time, and I use jklemmack's suggestion as well. And I do it for other purposes too, such as doing JOINs in UPDATEs (which SQLite3 does not support).

For example:

CREATE TABLE t(id INTEGER PRIMARY KEY, c1 TEXT NOT NULL UNIQUE, c2 TEXT);CREATE TABLE r(c1 TEXT NOT NULL UNIQUE, c2 TEXT);INSERT OR REPLACE INTO t (id, c1, c2)  SELECT t.id, coalesce(r.c1, t.c1), coalesce(r.c2, t.c2)  FROM r LEFT OUTER JOIN t ON r.c1 = t.c1  WHERE r.c2 = @param;

The WHERE there has the condition that you'd have in your IF. The JOIN in the SELECT provides the JOIN that SQLite3 doesn't support in UPDATE. The INSERT OR REPLACE and the use of t.id (which can be NULL if the row doesn't exist in t) together provide the THEN and ELSE bodies.

You can apply this over and over. If you'd have three statements (that cannot somehow be merged into one) in the THEN part of the IF you'd need to have three statements with the IF condition in their WHEREs.

This is called an UPSERT (i.e. UPdate or inSERT). It has its forms in almost every type of database. Look at this question for the SQLite version: SQLite - UPSERT *not* INSERT or REPLACE