Conditionally insert and return rowid or 0?

Question

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()

Answer 1

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.

Answer 2

Do nothing. Your query already returns 0 if there was no update.

The last_insert_rowid documentation says

The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

And the documentation for sqlite3_last_insert_rowid says

If no successful INSERTs ... have ever occurred ..., then sqlite3_last_insert_rowid(D) returns zero.