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

Since it seems the current solution involves creating a new table with id UNIQUE, moving everything over, dropping the old table and renaming the new table all while ensuring that it happens one time on the device before they try to do anything on the database and that it ONLY happens one time... isn't a currently valid solution for us, I'll be adding code to manually check the database before inserting the record. Hindsight being what it is, next time I'll make sure when syncing something to the server the actualid is autoincrementing and the id is unique. — Twomz, Mar 21 '13 at 13:40

score 10 · Accepted Answer · answered Mar 21 '13 at 07:14

10

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

answered Mar 21 '13 at 07:14

CL.

173,858
17
217
259

actualid is the autoincremented primary key for the device, id is a nonunique key obtained from the server. Hindsight being what it is we should have made id UNIQUE, but it isn't... can I use a pragma to make a column unique after the table has been created? – Twomz Mar 21 '13 at 13:13
2

`CREATE UNIQUE INDEX indexname ON tblTest(id);` (and if you had a non-unique index on `id`, drop it) – CL. Mar 21 '13 at 13:21
Sweet, I didn't know you could enforce uniqueness through an index. It's working on the test database I made, so I'll be implementing this fix instead of manually getting all the ids and checking them (Yay!). – Twomz Mar 21 '13 at 14:45

score 5 · Answer 2 · answered Mar 20 '13 at 21:47

5

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

answered Mar 20 '13 at 21:47

Mike Dinescu

54,171
16
118
151

That correctly doesn't duplicate records that are there, but a new record causes it to make more than one insert. That might be because I don't care if the name is duplicated only 'id'. – Twomz Mar 20 '13 at 21:57

Insert only if id doesn't exist

2 Answers2

Linked