Insert or Update data into tables in SQlite

Question

Am working in HTML5 + PhoneGap Applications with SQlite

For updating the table in SQlite ; am using REPLACE INTO method.

Here is example code ; : http://www.sqlfiddle.com/#!7/ccc33/2

Schema

CREATE TABLE mytable (
id integer primary key autoincrement,
name VARCHAR(10),
date DATE,
job VARCHAR(16),
hours VARCHAR(16)
);
CREATE UNIQUE INDEX myindex ON mytable(NAME);

Here NAME is the UNIQUE INDEX vale.

Query

REPLACE INTO mytable (NAME, DATE, JOB, HOURS)

VALUES ('BOB', '12/01/01', 'PM','20');

REPLACE INTO mytable (NAME, DATE, JOB, HOURS)

VALUES ('BOB', '12/01/01', 'PM','32');

REPLACE INTO mytable (NAME, DATE, JOB, HOURS)

VALUES ('BOB', '14/01/01', 'PM','35');

REPLACE INTO mytable (NAME, DATE, JOB, HOURS)

VALUES ('BOBg', '12/01/01', 'PM','350');

SELECT * FROM mytable;

But when Run the Query ; i seen that the primary key id is incremented . But my need its only update when NAME is same.

Is there any solution for this? Please help!

Answer 1

In your query when there is a unique constraint on (NAME, DATE, JOB, HOURS) or their combination, and it is violated, the records gets deleted and inserted with the new values. So the key id is incremented

If you want to something like and UPSERT do it like:

INSERT OR REPLACE INTO mytable (id, NAME, Date, JOB, HOURS)
 SELECT old.id, new.NAME, new.Date, new.JOB, new.HOURS
 FROM ( SELECT
     'BOB' AS name,
     '12/01/01' AS NAME,
     'PM'  AS JOB,
     '32'  AS JOB
 ) AS new
 LEFT JOIN (
     SELECT id, NAME, Date, JOB, HOURS
     FROM mytable
 ) AS old ON new.NAME= old.NAME;

Answer 2

Dont write autoincrement while creating id column.

http://sqlite.org/autoinc.html