How to do Update if record exists and insert if not in SQLite?

Question

Here is my statement:

CASE
    WHEN
        EXISTS
        (
            SELECT
                1 
            FROM
                Transactions 
            WHERE
                Hash = 'VLEYCBLTDGGLHVQEWWIQ' 
                AND Time = 1531739096
        )
    THEN
        UPDATE
            Transactions 
        SET
            BlockID = 0, PoolHeight = NULL 
        WHERE
            Hash = 'VLEYCBLTDGGLHVQEWWIQ' 
            AND Time = 1531739096 
    ELSE
        INSERT INTO Transactions (Hash, Time, _fROM, Signature, Mode, BlockID, OutputValue, PoolHeight) 
            VALUES('VLEYCBLTDGGLHVQEWWIQ', 1531739096, 'GENESIS', 'GENESIS', - 1, 0, 0, NULL)
END;

The error:

System.Data.SQLite.SQLiteException: 'SQL logic error near "CASE": syntax error'

Basically: If Exists is true, then i want to update the record. If it doesnt exist i want to insert the record.

What about [this thread's answer](https://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace)? — Rigerta, Jul 16 '18 at 12:24
i dont think this is working because i dont use the primary key here. Honestly if() else() would be just perfect. I just want to know why my expression has a syntax error. — Loading, Jul 16 '18 at 12:30
What is this enclosed in or is this the entire statement? I do not think you can start a statement with CASE. — Igor, Jul 16 '18 at 12:32
Also is there any change if you also enclose both the UPDATE and INSERT commands with elipses `(update...) else (insert...) END`? — Igor, Jul 16 '18 at 12:36

Bacon Bits · Accepted Answer · 2018-07-16T12:58:33.973

You will need to do that in your application. The CASE expression cannot be used for control flow. Many RDBMSs support an IF statement for that purpose, but SQLite does not. SQLite's dialect of SQL does not support control flow with SQL.

For this problem, you can try using SQLite's INSERT OR REPLACE syntax, but looking at your queries it does not seem to 100% match what you're trying to do. You'd be updating Signature, Mode, BlockID, OutputValue, and PoolHeight on a collision, while it looks like you only want to update BlockID and PoolHeight.

You may be able to use an INSERT [...] ON CONFLICT UPDATE statement, but I haven't used the so-called UPSERT clause before. I believe it would look like this:

INSERT INTO Transactions (Hash, Time, _fROM, Signature, Mode, BlockID, OutputValue, PoolHeight) 
VALUES('VLEYCBLTDGGLHVQEWWIQ', 1531739096, 'GENESIS', 'GENESIS', - 1, 0, 0, NULL)
ON CONFLICT (Hash, Time) DO UPDATE SET BlockID = 0, PoolHeight = NULL 
    WHERE Hash = 'VLEYCBLTDGGLHVQEWWIQ' AND Time = 1531739096;

However, I've never used this statement myself on SQLite, so I'm not sure of exactly how it behaves. It does seem to require that the columns specified for CONFLICT are indexed. I'm not entirely sure the WHERE clause is even necessary here. Read the doc and do extensive testing first.

The other option would be to simply run the UPDATE statement every time, and then if you get zero records affected run the INSERT.

> The other option would be to simply run the UPDATE statement every time, and then if you get zero records affected run the INSERT. This is what i did and it works completly fine. Thank you. Sorry for the late answer — Loading, Jul 23 '18 at 09:44
The drawback of this query is this will unnecessarily increase the Primary Key counter every time conflict occurs. — Akshay Bhimani, Jun 12 '23 at 07:54

How to do Update if record exists and insert if not in SQLite?

1 Answers1