Insert only if id doesn't exist

Question

Alright, so we have a phonegap app with a table setup like

tblTest (actualid INTEGER PRIMARY KEY, id INTEGER, name TEXT)

The actualid is a unique id for the device and the id is maintained in a server side database. We've had issues with webservices returning duplicate records and while we're fixing that I wanted to add something to our sql that would prevent duplicates from being added (fixing bad data is a pain).

Our current insert statement is setup like

INSERT INTO tblTest (id, name) VALUES (101, 'Bob')

If you run that twice the database will end up looking like

actualid |  id| name
       1 | 101|  Bob
       2 | 101|  Bob

And what I'd like for it to look like is

actualid |  id| name
       1 | 101|  Bob

Insert or Replace would give me an actualid 2 in the example and any example I've found using a where clause was setup like

INSERT INTO tblTest SELECT ..... WHERE.....

Which doesn't work because none of the data is in a table yet (unless I'm making a newbie mistake, I'm not very good at sqlite or general sql).

Answer 1

You might want to try this:

 INSERT INTO tblTest 
      (id, name)
      SELECT 101 as id, 'Bob' as name
        FROM tblTest
       WHERE NOT EXISTS(SELECT * FROM tblTest WHERE id = 101 and name = 'Bob')

Answer 2

Use INSERT OR IGNORE:

INSERT OR IGNORE INTO tblTest (id, name) VALUES (101, 'Bob')

(This requires a unique index on the id column, which you already have.)