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 lessoninfoWHERE Lesson="+lesson_no+")))This gives an error saying:
db error near where syntax.
Best Answer
If you never want to have duplicates, you should declare this as a table constraint:
CREATE TABLE bookmarks(users_id INTEGER,lessoninfo_id INTEGER,UNIQUE(users_id, lessoninfo_id));(A primary key over both columns would have the same effect.)
It is then possible to tell the database that you want to silently ignore records that would violate such a constraint:
INSERT OR IGNORE INTO bookmarks(users_id, lessoninfo_id) VALUES(123, 456)The INSERT IF NOT EXISTS statement in SQLite allows you to insert data into a table only if the data does not already exist in the table. This can be useful to prevent duplicate entries and maintain data integrity. The syntax for the INSERT IF NOT EXISTS statement is as follows:
INSERT OR IGNORE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);By adding the 'OR IGNORE' clause after the INSERT statement, SQLite will ignore the insertion if a conflict occurs. The conflict in this case is when a unique constraint or primary key violation is encountered.
For example, let's say we have a 'users' table with columns 'id' (primary key) and 'name'. To insert a new user into the table only if the user does not already exist, we can use the following query:
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'John Doe');If a user with the same ID already exists, the insertion will be ignored and no error will be thrown.
Using the INSERT IF NOT EXISTS statement can help you avoid duplicate entries and ensure data consistency in your SQLite database. It is a useful tool for efficient data insertion and management.
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');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.
I would advice that you instead design your table so that no duplicates are allowed as explained in @CLs answer below.
For a unique column, use this:
INSERT OR REPLACE INTO tableName (...) values(...);For more information, see: sqlite.org/lang_insert
insert into bookmarks (users_id, lessoninfo_id)select 1, 167EXCEPTselect user_id, lessoninfo_idfrom bookmarkswhere user_id=1and lessoninfo_id=167;This is the fastest way.
For some other SQL engines, you can use a Dummy table containing 1 record.e.g:
select 1, 167 from ONE_RECORD_DUMMY_TABLE