insert with returning question
(1) By anonymous on 2021-05-03 15:53:29 [source]
Hello,
If I have a table like this: create table if not exists table1(a text, b integer);
I can add rows like this: insert into table1(a, b) values("a", 1), ("b", 2), ... ;
And query them like this: select rowid, a, b from table1;
If I now do this: insert into table1(a, b) values("a", 1) returning rowid;
No row will be returned. If a new record is added, the rowid will be returned.
I'm looking for a way to always have a row returned. In other words, to form a "get or create" kind of statement. I realize I can run two queries to do this, but it seems like it would be more efficient if I could do this with just a single query.
Any suggestions?
(2) By Harald Hanche-Olsen (hanche) on 2021-05-03 16:14:41 in reply to 1 [link] [source]
I don't get the behaviour you describe from sqlite.
Since your table has no primary keys or unique constraints, INSERT will always insert a new row, and the RETURNING clause will indeed give you the new rowid.
If you made the a column, say, a primary key, and use INSERT OR REPLACE, you also get rows inserted and rowids returned.
So perhaps you are thinking of INSERT OR IGNORE, and you would like a single sql command INSERTing og IGNOREing, respectively, but returning a rowid in either case? Honestly, I don't know if you can. But can you tell us whether my interpretation is correct, or I have misunderstood the question completely?
(3) By anonymous on 2021-05-03 16:59:28 in reply to 2 [link] [source]
My apologies. Indeed, I did forget to add the "or ignore" qualifier. And, your interpretation is correct.
In C++ systems I write, I tend to create code that looks something like this:
class Vertex;
class Graph { ... Vertex* GetOrCreateVertex(const std::string& rVertexName); ... }
Vertex* pV = pGraph->GetOrCreateVertex("kitchen"); assert(pV != 0); pV->SetRoomSize(20.5 * 24.5);
The GetOrCreate pattern makes code straightforward to write. All I'm after is something that works in a similar fashion for a sqlite database. I know about the UPSERT statement but this isn't quite the same thing as a "get or create" statement.
Again, if I have to use two different statements to make this work, that's not a problem. I was simply wondering if there is way to do this with just a single statement.