SQLITE - INSERT or UPDATE without changing ROWID value

user_1054151 0
Viewed 7017

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")
sql-update sqlite
4 Answers

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')
user_11293320

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;
user_64686480
Related