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

Answer 1

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.

Answer 2

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.