“如果不存在则插入”语句在SQLite [英] "Insert if not exists" statement in SQLite
问题描述
我有一个sqlite数据库。我试图在表书签中插入值(users_id,lessoninfo_id),前提是它们在一行中不存在。
I have a sqlite database. I am trying to insert values(users_id,lessoninfo_id) in table bookmarks, only if both do not exists 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+")))
这会给出错误,说明在附近的语法的db错误。
This gives error saying 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
等于'要插入的文本', 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.
这篇关于“如果不存在则插入”语句在SQLite的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!