有条件地插入并返回rowid还是0？

发布于 2025-02-12 07:48:12 字数 267 浏览 1 评论 0原文

我知道如何使用交易来执行此操作，但我想知道我是否可以在一行中进行此操作。我的实际查询更为复杂，但我不知道的部分是如何获取rowID或0，而无需重复wery子句

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

好听的两个字的网名 2025-02-19 07:48:14

如果您的SQLite版本为3.35.0+，则可以使用 a> 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插入查询什么都没有返回。
If your app can check the number of returned rows you can translate that as 0.

See the demo< /a>。

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.

回复收藏 0 原文