I have a sqlite table:
CREATE TABLE TEST (id integer primary key, number integer unique, name text);
However, i need to make REPLACE without autoincrementing the id field when it updates.
For example : THE TABLE IS EMPTY
REPLACE INTO TEST (null, 23, 'Bill');
REPLACE INTO TEST (null, 52, 'Jack');
When I execute the query,
SELECT * FROM TEST; I get...
if I do...
REPLACE INTO TEST VALUES (null, 52, 'John');
from the query
SELECT * FROM TEST; i get..
but i need...
Does anyone have an idea on how do do this ?
This is not possible with a single command.
REPLACE always deletes the old record (if it exists) before inserting the new one.
To keep the autoincrement value, you have to keep the record. That is, update the old record in place, and insert a new one only if no old one existed:
db.execute("UPDATE Test SET Name = 'John' WHERE Number = 52") if affected_records == 0: db.execute("INSERT INTO Test(Number, Name) VALUES(52, 'John')")