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

A procedural-`if` can always be done client-side provided the use of transactions (or other atomic guarantees). Anyway, this is an "upsert" see [SQLite - UPSERT *not* INSERT or REPLACE](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace) -- the answer by Eric B, in particular... note the use of `coalesce` to act as a "limited if". — , Oct 22 '11 at 18:52
SQLite doesn't support `IF ELSE END` clauses, right? Are you asking if you can achieve this in SQLite? SQLite supports some if-like capabilities with `CASE` but I don't think it will solve your issue. http://stackoverflow.com/questions/1294619/does-sqlite-supports-any-kind-of-ifcondition-statement-in-the-select — Gray, Oct 27 '11 at 16:26

score 6 · Answer 1 · edited May 23 '17 at 10:30

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

To clarify, this seems to work if Word and Topic are set to UNIQUE, otherwise it creates a new entry? — Ian Clay, Nov 19 '12 at 03:43

score 3 · Answer 2 · answered Mar 05 '12 at 23:25

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.

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

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

edited May 23 '17 at 10:30

Community

1
1

answered Jun 12 '13 at 18:49

chacham15

13,719
26
104
207

Arsen Khachaturyan · Answer 4 · 2020-01-15T13:13:48.297

0

One way that I've found is based on SQL WHERE clause true/false statement:

 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 execute some QUERIES if some other QUERY returns 'any' result.

edited Jan 15 '20 at 13:13

answered Sep 14 '17 at 12:45

Arsen Khachaturyan

7,904
4
42
42

IF-Statement in SQLite: update or insert?

4 Answers4

Linked