[SOLVED] Conditionally insert and return rowid or 0?

Conditionally insert and return rowid or 0?

I know how to do this using a transaction but I was wondering if I can do this in a single line. My actual query is more complex but the part I can't figure out is how to get the rowid or 0 without repeating the where clause

insert into comment
(select @text, @userid, @date)
where (select count(*) from comment where body=@text and userid=@userid) == 0
select last_insert_rowid()

Solution

If your version of SQLite is 3.35.0+ you can use the RETURNING clause to get the rowid of the inserted row like this:

WITH cte(body, userid, date) AS (SELECT @text, @userid, @date)
INSERT INTO comment (body, userid, date)
SELECT c.* 
FROM cte c
WHERE NOT EXISTS (SELECT * FROM comment t WHERE (t.body, t.userid) = (c.body, c.userid))
RETURNING rowid;

The drawback is that in case of a failed insertion the query returns nothing.
If your app can check the number of returned rows you can translate that as 0.

See the demo.