SQLITE - INSERT or UPDATE without changing ROWID value

Question

I need to update a table row IF EXISTS, otherwise INSERT a new row. I tried:

INSERT OR REPLACE INTO table VALUES ...

but if the row row exist this statement changes the row's ROWID, and that's what I'm trying to avoid (I need the rowid :D)

I also tried to find a way to get some sort of return value from the update, in the case where an update has taken place, but I still don't understand how... If I could get the return value from the update statement, I could choose wether to proceed with an insert or not.

Do you have any suggestion or solution to this problem? Or do I need to make a copy of the ROWID and use that instead of the "pure" table ROWID?

Thanks in advance, best regards

ps: I was looking HERE and I was wondering if sqlite has the OUTPUT special word too, but google didn't help me..

---- EDIT after reading comments:

table schema example

CREATE TABLE test (
    table_id TEXT NOT NULL,
    some_field TEXT NOT NULL,
    PRIMARY KEY(table_id)
)

INSERT or REPLACE INTO test (table_id, some_field) VALUES ("foo","bar")

Can you post a table schema and a full "insert or replace" statement? — , Jul 30 '12 at 10:13
`ExecuteNonQuery` returns the number of rows affected, so if your update returns 0, do an insert. — Thomas, Jul 30 '12 at 10:16

score 4 · Answer 1 · answered Nov 27 '12 at 20:57

4

I tested Chris suggestion but the rowid still gets changed. I think the best alternative is to do a SELECT to see if a row with that key already exist. If so, UPDATE, otherwise, INSERT... good old fashion but guaranteed to work.

answered Nov 27 '12 at 20:57

SHamel

159
8

score 3 · Answer 2 · answered Oct 18 '17 at 10:50

3

Combine it with select, like this

INSERT or REPLACE INTO test (ROWID, table_id, some_field)
VALUES ((SELECT ROWID from test WHERE table_id = 'foo' UNION SELECT max(ROWID) + 1 from test limit 1), 'foo','bar')

answered Oct 18 '17 at 10:50

Ilya Gazman

29,832
19
128
206

score 1 · Answer 3 · answered Aug 03 '12 at 14:28

You need to specify that your table_id is unique in addition to being the primary key:

sqlite> CREATE TABLE test (
    table_id TEXT NOT NULL,
    some_field TEXT NOT NULL,
    PRIMARY KEY(table_id),
    UNIQUE(table_id)
);

sqlite> insert or replace into test values("xyz", "other");
sqlite> select * FROM test;
xyz|other
sqlite> insert or replace into test values("abc", "something");
sqlite> insert or replace into test values("xyz", "whatever");
sqlite> select * FROM test;
abc|something
xyz|whatever

score 0 · Answer 4 · answered Jan 12 '22 at 17:10

From version 3.24.0 (2018-06-04), SQLite now supports an UPSERT clause that will do exactly what the OP needed: https://www.sqlite.org/lang_UPSERT.html

The insert would now look like this:

INSERT INTO test (table_id, some_field) VALUES ("foo","baz")
ON CONFLICT(table_id) DO UPDATE SET some_field=excluded.some_field;

SQLITE - INSERT or UPDATE without changing ROWID value

4 Answers4