CODEWITHSUNDEEP
sqlitecordovaweb-sqlunique-index
ULLAS MOHAN.V
ULLAS MOHAN.V

Reputation: 1531

Insert or Update data into tables in SQlite

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 enter image description here . But my need its only update when NAME is same.

Is there any solution for this? Please help!

Upvotes: 2

Views: 1310

Answers (2)

Nejat
Nejat

Reputation: 32665

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;

Upvotes: 1

Anjy786
Anjy786

Reputation: 145

Dont write autoincrement while creating id column.

http://sqlite.org/autoinc.html

Upvotes: 0

Related Questions