Inserting values only if the record doesn't exist already

Question

am using sqlite3 database. I have inserted set of device_ids(unique) in a table called device, this is done when the table is empty. But when i try to insert the ids again which contains some new id's it is throwing error because of uniqueness.

 device_all_id.each do |device_id|

     insert into device (Device_id) values ('#{device_id.first}') where  where Device_id <> '#{device_id.first}'

 end

So i want to check whether the record is already there before inserting device_ids, so i tried 'IF EXISTS' query but that too throwing as syntax error.

could anyone please help me out of this..

Please don't use tags that don't relate to your question – John Conde Apr 06 '13 at 16:59 — John Conde, Apr 06 '13 at 16:59
ok i won't.. thanks for the info – Abhiram Apr 06 '13 at 18:57 — Abhiram, Apr 06 '13 at 18:57

score 1 · Answer 1 · edited May 23 '17 at 11:56

Checking before inserting will require the database to answer two queries, and it won't really reduce your error handling. (You have to trap errors. There are a lot of ways an update can go wrong.) You're almost always better off just

executing the INSERT statement,
trapping errors, and
taking appropriate action to resolve the error.

It's not clear from your question whether an UPSERT might work for you. See this SO answer.

Inserting values only if the record doesn't exist already

1 Answers1