SQLITE: Replace without auto_increment

问题:

I have a sqlite table: for example

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

1|23|Bill
2|52|Jack

if I do...

REPLACE INTO TEST VALUES (null, 52, 'John');

from the query SELECT * FROM TEST; i get..

1|23|Bill
3|52|John

but i need...

1|23|Bill
2|52|John

Does anyone have an idea on how do do this ?

回答1:

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