SQLite IF-Statement: update or insert?

Question

I cannot run this query using 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 next to IF" How can I solve the problem?

Answer 1

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

Insetad, check out the ERIC B offer on another thread . You effectively look at UPSERT (UPdate if record exists, INSERT if not). Eric B. has a good example of how to do this in SQLite syntax using the "INSERT OR REPLACE" functionality in SQLite. Basically, you would 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 INSERT ... SELECT ... WHERE ... EXISTS [or not] (SELECT ...);

I do this all the time and I also use the jklemmack suggestion. And I do this for other purposes too, for example, for JOINs in UPDATE (which does not support SQLite3).

For instance:

 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; 

WHERE there is a condition that you would have in your IF. JOIN in SELECT provides a JOIN that SQLite3 does not support in UPDATE. INSERT OR REPLACE and using t.id (which can be NULL if the string does not exist in t) together provide the THEN and ELSE bodies.

You can apply it again and again. If you have three statements (which cannot be combined into one) in the THEN part of IF, you will need to have three statements with the IF clause in your WHERE clause.

Answer 3

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

Answer 4

One of the ways I found is based on the expression of the WHERE WHERE clause true / false:

 SELECT * FROM SOME_TABLE WHERE ( SELECT SINGLE_COLUMN_NAME FROM SOME_OTHER_TABLE WHERE SOME_COLUMN = 'some value' and SOME_OTHER_COLUMN = 'some other value' ) 

This actually means executing some query if any other query returns the result "any".