SELECT and RETURNING in one statement
(1) By Marco Bubke (marcob) on 2021-03-15 11:28:34 [link] [source]
I want to optimize my code a little bit. So far I was inserting always to trigger a hook for the rowid but I think it is not so smart because I will always write even if 90% of the entries are not changed. So I thought to test if they are equal. I think one lookup would be better than two.
CREATE TABLE IF NOT EXISTS translatables(id INTEGER NOT NULL UNIQUE, translationTextId TEXT PRIMARY KEY NOT NULL CHECK (translationTextId != ''))
I know the following code is not working but I think it shows my intention.
SELECT id FROM translatables WHERE translationTextId=?2 UNION ALL INSERT INTO translatables (id, translationTextId) (?1, ?2) ON CONFLICT(translationTextId) DO NOTHING RETURNING id; LIMIT 1
So is there a way to archive that? I found this PostgreSql solution but to my understanding this is not working in Sqlite.
Later I use the ids to delete all not inserted or "updated" rows.
(2) By Larry Brasfield (larrybr) on 2021-03-15 12:28:48 in reply to 1 [source]
SQLite does not yet support use of RETURNING clause results in any kind of more-outer query or DML.