UPSERT in SQLite

Question

I don't want to use REPLACE INTO because it's basically a DELETE and INSERT and it's complicated to use the data from the old columns.

INSERT OR IGNORE is a bit of a hack because all errors are ignored, so this is not an option.

I've read a blog article which uses the following:

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

I like this approach really much, but I don't know how I can implement this IF-clause with the @@ROWCOUNT in SQLite, this is what I got:

SELECT CASE (SELECT
    CASE
        WHEN EXISTS(SELECT 1 FROM t WHERE id=2)
            THEN 1
            ELSE 0
    END)
WHEN 1
  UPDATE t set a='pdf' WHERE id=2;
ELSE
  INSERT INTO t (a) VALUES ('pdf');
END

the SELECT CASE seems to be the only way to use a CASE-clause in SQLite because everything else throws an syntax error. But it's also not possible to use a UPDATE- or INSERT-statement in the SELECT CASE, so this throws an error.

I've tried the following

UPDATE t set a='pdf' WHERE id=2;
CASE WHEN (changes()=0) THEN
    INSERT INTO t (a) VALUES ('pdf');
END

but this doesn't work, because the CASE-clause throws an syntax error.

can someone provide an example using @@ROWCOUNT for an UPSERT in SQLite?

`@@ROWCOUNT` is a feature of T-SQL not available in SQLite, nor does SQLite have the IF or CASE statements as you're trying to use them. If you really need UPSERT you may need to accept that "it's complicated" and either use the [solution given in question 418898](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace/4330694#4330694) or check the result of `SELECT changes()` from application code and perform the INSERT based on that. — Stephen Jennings, Dec 02 '13 at 08:21
The `@@ROWCOUNT` is borrowed from the blog article I refer to, sorry for the bad articulation. I like the idea with `SELECT changes()`, thanks — ultima_rat0, Dec 02 '13 at 15:42

CL. · Accepted Answer · 2017-08-09T06:39:10.540

11

SQLite has no built-in UPSERT-like statement that doesn't delete the old record.

You should check the number of changes in your program, and execute the INSERT conditionally.

However, if you really want to do this in SQL, it's possible; but you have to use the INSERT ... SELECT ... form so that you are able to insert zero records, if needed:

BEGIN;
UPDATE t SET a = 'pdf' WHERE id = 2;
INSERT INTO t(id, a) SELECT 2, 'pdf' WHERE changes() = 0;
COMMIT;

edited Aug 09 '17 at 06:39

answered Dec 02 '13 at 11:02

CL.

158,085
15
181
214

Why is everbody using `REPLACE INTO` or `INSERT OR IGNORE` then? This solution works like a charm and looks beautiful, thanks! – ultima_rat0 Dec 02 '13 at 15:43
`INSERT OR IGNORE` is using because it consumes single SQL statement. – Mrug Apr 01 '15 at 05:12
what if another thread was updating a row and changes() was changed between the two query statement? – alizx Aug 09 '17 at 05:59
@alizx When using a transaction, other threads cannot interfere. – CL. Aug 09 '17 at 06:39
according to documentation its per database connection not transaction. – alizx Aug 09 '17 at 07:24
"If a separate thread makes changes on the same database connection while sqlite3_changes() is running then the value returned is unpredictable and not meaningful." SQLite documentation – alizx Aug 09 '17 at 07:33
@alizx When two threads share a connection, they also share the connection's transaction. Proper multithreading requires using using separate connections. – CL. Aug 09 '17 at 07:35
@CL. I cannot get what does sharing a transaction mean. can you please elaborate on this? – alizx Aug 09 '17 at 07:40
1

@alizx There is a 1:1 relationship between a connection and a transaction. If you want separate transactions, you need separate connections. – CL. Aug 09 '17 at 07:41

score 2 · Answer 2 · answered Dec 02 '13 at 09:10

You should use sqlite API in this case and write "IF logic" in your application.

sqlite3_prepare16_v2(stmt1, "UPDATE t SET a=? WHERE id=?");

sqlite3_prepare16_v2(stmt2, "INSERT INTO t(id, a) VALUES(?, ?)");

for(...) // iterate by rows to be updated/inserted
{
    //set parameter values for stmt1

    sqlite3_step(stmt1);
    if( !sqlite3_changes(dbh) )
    {
        //set parameter values for stmt2
        sqlite3_step(stmt2);
    }
}

UPSERT in SQLite

2 Answers2

Linked