Paste only if id does not exist

Question

Ok, so we have a phonegap application with table settings, for example

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

The actual value is a unique identifier for the device, and the identifier is maintained in the server-side database. We had problems with web services returning duplicate records, and although we fix that I wanted to add something to our sql to prevent duplicates from being added (fixing bad data is a pain).

Our current insert statement is set as

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

If you run this twice, the database will look like

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

And I would like it to look like

 actualid | id| name 1 | 101| Bob 

The Insert or Replace will give me an actualid 2 example in this example, and any example I found using the where clause was configured as

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

Which does not work, because none of the data is listed in the table yet (if I do not make a beginners mistake, I am not very good at sqlite or general sql).

Answer 1

Use INSERT OR IGNORE :

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

(This requires a unique index in the id column that you already have.)

Answer 2

You might want to try the following:

  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')