IF-Statement in SQLite: update or insert?

Question

I Can't run this query with SQLite

if 0<(select COUNT(*) from Repetition where (Word='behnam' and Topic='mine'))
begin
 update Repetition set Counts=1+ (select Counts from Repetition where (Word='behnam' and Topic='mine'))
end
else
begin
    insert Repetition(Word,Topic,Counts)values('behnam','mine',1)
end

It says "Syntax error near IF" How can I solve the problem

Answer 1

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)
       )

Answer 2

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.