I'm trying to avoid writing different SQL queries to achieve the following scenario:
I have a Table called Values:
id INT (PK) data TEXT
I would like to check if a certain data exists in the table, if it is return it's id, if it does not exists, then insert it and return it's id.
The (very) naive way would be:
select id from Values where data = "SOME_DATA";
if id is not null, great use 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?
Reminder, this is a sqlite3 engine! :P