Conditionally insert and return rowid or 0?-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.