UPSERT in SQLite
sql, sqlite, upsert
Solution
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;
Problem
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?