Sqlite - SELECT or INSERT and SELECT in one statement

Question

I'm trying to avoid writing separate SQL queries to achieve the following scenario:

I have a Table called Values:

Values:

id INT (PK)
data TEXT

I would like to check if certain data exists in the table, and if it does, return its id, otherwise insert it and return its id.

The (very) naive way would be:

select id from Values where data = "SOME_DATA";

if id is not null, take it. if id is null then:

insert into Values(data) values("SOME_DATA");

and then select it again to see its id or use the returned id.

I am trying to make the above functionality in one line. I think I'm getting close, but I couldn't make it yet: So far I got this:

select id from Values where data=(COALESCE((select data from Values where data="SOME_DATA"), (insert into Values(data) values("SOME_DATA"));

I'm trying to take advantage of the fact that the second select will return null and then the second argument to COALESCE will be returned. No success so far. What am I missing?

Answer 1

Your command does not work because in SQL, INSERT does not return a value.

If you have a unique constraint/index on the data column, you can use that to prevent duplicates if you blindly insert the value; this uses SQLite's INSERT OR IGNORE extension:

INSERT OR IGNORE INTO "Values"(data) VALUES('SOME_DATE');
SELECT id FROM "Values" WHERE data = 'SOME_DATA';