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 or check the result of SELECT changes() from application code and perform the INSERT based on that. — Stephen Jennings
– Stephen Jennings, Commented Dec 2, 2013 at 8: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
– ultima_rat0, Commented Dec 2, 2013 at 15:42

CL. · Accepted Answer · 2017-08-09 06:39:10Z

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 9, 2017 at 6:39

answered Dec 2, 2013 at 11:02

CL.

182k18 gold badges241 silver badges283 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ultima_rat0 Over a year ago

Why is everbody using REPLACE INTO or INSERT OR IGNORE then? This solution works like a charm and looks beautiful, thanks!

Mrug Over a year ago

INSERT OR IGNORE is using because it consumes single SQL statement.

alizx Over a year ago

what if another thread was updating a row and changes() was changed between the two query statement?

CL. Over a year ago

@alizx When using a transaction, other threads cannot interfere.

CL. Over a year ago

@alizx There is a 1:1 relationship between a connection and a transaction. If you want separate transactions, you need separate connections.

|

WadimX · Accepted Answer · 2013-12-02 09:10:54Z

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

Collectives™ on Stack Overflow

UPSERT in SQLite

2 Answers 2

9 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Linked

Related