SQLite Forum

Returning and insert with conflict
Login

Returning and insert with conflict

(1) By Marco Bubke (marcob) on 2021-02-11 12:57:16 [link] [source]

Hello

I have recurring pattern where I overwrite something and then remove the non updated and inserted rows. For that I get all rowids and then at the end use carray to delete the ones which are not in the array.

But what happens if I write?

INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid
INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO NOTHING RETURNING rowid

Is the rowid returned if there was already an entry? To my understanding it will not. Maybe I could work around by adding a dummy column but it would be very handy.

Maybe a different syntax like would be better?

INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO RETURNING rowid

Best regards, Marco

(2) By Marco Bubke (marcob) on 2021-02-11 13:14:28 in reply to 1 [link] [source]

I don't know if Sqlite is optimizing the writing case where the old and the new value is the same? I am not even sure if that has an advantage but I think it would be very similar to this because the row would be not changed.

(3) By Larry Brasfield (larrybr) on 2021-02-11 14:41:34 in reply to 1 [source]

... what happens if I write?

INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid

INSERT INTO my_table(foo) VALUES (?) ON CONFLICT DO NOTHING RETURNING rowid

Is the rowid returned if there was already an entry?

I would expect no returned result from attempting data modification where it cannot occur. However, the DML with a RETURNING clause ("DMLR") is in development and so it is premature to ask what happens, as if that was the final answer. We might discuss what happens now, and how desirable that or something else is.

In my opinion, DMLR which yields only data modification attempts should not return anything. The pre-v3.35 build I have acts that way. To wit: [C:Tmp] > type dmlr_conflict.sql create table t (species text unique); insert into t values ('dog'); insert into t values ('cat'); insert into t values ('pig') on conflict do nothing returning rowid, species; insert into t values ('dog') on conflict do nothing returning rowid, species; drop table t [C:Tmp] > echo .q | sqlite3m -batch -cmd ".read dmlr_conflict.sql" rowid species 3 pig rowid species [C:Tmp] > As you can see, the last DMLR input produced an empty [a] result set.

[a. The 'sqlite3m' shell is like sqlite3.exe except it emits row headers even if the result set was empty. ]

Maybe a different syntax like would be better?

A simple query would get the set of rowid you desire. If the DMLR feature (which is experimental and evolving) should produce results that can be put into a temp table or used directly in queries, you could easily write that simple query using those RETURNING results. I think that is a much smaller imposition on users than adding further syntax complexity.

(4) By Marco Bubke (marcob) on 2021-02-11 16:43:19 in reply to 3 [link] [source]

Yes, I could add an SELECT before every INSERT but that would increase the complexity of code. Maybe a CTE is working.

WITH cte(rowid) AS (INSERT OR IGNORE INTO my_table(foo) VALUES (?) RETURNING rowid UNION ALL SELECT rowid FROM mytable WHERE foo = ?) SELECT rowid FROM cte

But I am not sure you can even do that. Anyway in this case I would go for the dummy column because the code would be more readable.