IF ELSE Condition in SQLite

Question

I am new to SQLite.

I am looking for IF ELSE in SQLite to perform UPDATE OR INSERT operation based on the condition check.

What I tried is:

IF EXISTS(SELECT 1 FROM TblTest WHERE ID = 1 AND Name = 'XYZ')
BEGIN
END
    UPDATE TblTest
    SET Address = 'NYC',
    Mobile='102938340'
    WHERE ID = 1 AND Name='XYZ'
ELSE
BEGIN
INSERT INTO TblTest(ID,Name,Address,Mobile)
    VALUES(1,'XYZ','NYC','102938340');
END

Getting an error:

Result: near "IF": syntax error

That's not valid sqlite syntax. See https://www.sqlite.org/lang.html for the SQL dialect it understands. — Shawn, Sep 18 '19 at 07:55
I *think* you're looking for what's known as [UPSERT](https://www.sqlite.org/lang_UPSERT.html). — Shawn, Sep 18 '19 at 07:57

score 1 · Answer 1 · answered Sep 18 '19 at 07:51

If you had a unique index on (ID, Name), you could take advantage of SQLite's REPLACE syntax:

CREATE UNIQUE INDEX idx_id_name ON TblTest (ID, Name);

INSERT OR REPLACE INTO TblTest (ID, Name, Address, Mobile)
VALUES
    (1, 'XYZ', 'NYC', '102938340');

This would have the effect that if a record with ID=1 and Name=XYZ exist in the table already, an update would be performed. Otherwise, a new record would be inserted.

The unique index on both ID (presumably the current primary key) and Name might seem redundant. However, this combination should ideally always be unique anyway.

score 1 · Accepted Answer · answered Sep 18 '19 at 09:48

If ID and Name are not a unique combination for your table and so you can't use INSERT OR REPLACE, you can execute these statements:

UPDATE TblTest
SET Address = 'NYC',
    Mobile='102938340'
WHERE ID = 1 AND Name='XYZ';

INSERT INTO TblTest(ID,Name,Address,Mobile)
SELECT 1,'XYZ','NYC','102938340'
WHERE NOT EXISTS(SELECT 1 FROM TblTest WHERE ID = 1 AND Name = 'XYZ');

If there is no row in the table with ID = 1 AND Name = 'XYZ' then the UPDATE statement will not update anything because the condition in the WHERE clause will return nothing. But the INSERT statement will insert the new row.
If there is a row with ID = 1 AND Name = 'XYZ' then the UPDATE statement will update it and the INSERT statement will not insert anything because NOT EXISTS will return FALSE.

Clever answer, but note that this should be run inside a transaction to ensure that the update and insert occur together atomically. — Tim Biegeleisen, Sep 18 '19 at 09:56

IF ELSE Condition in SQLite

2 Answers2