“如果不存在则插入"SQLite 中的语句 [英] "Insert if not exists" statement in SQLite

问题描述

我有一个 SQLite 数据库.我正在尝试在表 bookmarks 中插入值(users_id、lessoninfo_id)，前提是两者之前连续不存在.

I have an SQLite database. I am trying to insert values (users_id, lessoninfo_id) in table bookmarks, only if both do not exist before in a row.

INSERT INTO bookmarks(users_id,lessoninfo_id) 
VALUES(
    (SELECT _id FROM Users WHERE User='"+$('#user_lesson').html()+"'),
        (SELECT _id FROM lessoninfo 
        WHERE Lesson="+lesson_no+" AND cast(starttime AS int)="+Math.floor(result_set.rows.item(markerCount-1).starttime)+") 
        WHERE NOT EXISTS (
            SELECT users_id,lessoninfo_id from bookmarks 
            WHERE users_id=(SELECT _id FROM Users 
            WHERE User='"+$('#user_lesson').html()+"') AND lessoninfo_id=(
                SELECT _id FROM lessoninfo
                WHERE Lesson="+lesson_no+")))

这给出了一个错误说:

靠近 where 语法的数据库错误.

db error near where syntax.

推荐答案

如果你有一个名为 memos 的表格，它有两列 id 和 text 你应该能够这样做:

If you have a table called memos that has two columns id and text you should be able to do like this:

INSERT INTO memos(id,text) 
SELECT 5, 'text to insert' 
WHERE NOT EXISTS(SELECT 1 FROM memos WHERE id = 5 AND text = 'text to insert');

如果记录已经包含一行，其中 text 等于 'text to insert' 并且 id 等于 5，那么插入操作将被忽略.

If a record already contains a row where text is equal to 'text to insert' and id is equal to 5, then the insert operation will be ignored.

我不知道这是否适用于您的特定查询，但也许它可以为您提供有关如何进行的提示.

I don't know if this will work for your particular query, but perhaps it give you a hint on how to proceed.

我建议您改为设计表格，以便按照 @CLs answer 下面.

I would advice that you instead design your table so that no duplicates are allowed as explained in @CLs answer below.

这篇关于“如果不存在则插入"SQLite 中的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！