1

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()
forpas
  • 160,666
  • 10
  • 38
  • 76
Cal
  • 121
  • 8
  • Is there a unique constraint on the columns (body, userid)? – forpas Jul 02 '22 at 20:18
  • @forpas no. I want to prevent double posting in case the user hits submit 5 times on accident. But the same comment can be said multiple times if they're in different threads or a few hours apart (I removed that to shorten the example) – Cal Jul 02 '22 at 20:20

2 Answers2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thats pretty neat. I had no idea sqlite supported cte. It's been a while since I touched a DB – Cal Jul 02 '22 at 22:15
-1

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I reuse the same connection for multiple queries. Can I reset the id some how? – Cal Jul 02 '22 at 20:25
  • @cal maybe not, but you could try it in a new transaction. – Bohemian Jul 02 '22 at 20:32
  • @Bohemian The part you stripped out of your second quote makes it pretty clear that a transaction won't reset the last insert rowid to zero... "If no successful INSERTs into rowid tables have ever occurred on the database connection D, then sqlite3_last_insert_rowid(D) returns zero." – Colonel Thirty Two Jul 02 '22 at 20:56
  • @ColonelThirtyTwo OP didn't say the connection had prior use, and I only said "you could try" a transaction. Opening a new connection for this purpose is not the worst solution. It is sqlite being used so the micro performance hit of opening a new connection is almost certainly not a problem. – Bohemian Jul 02 '22 at 23:38