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

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

Solution 2:

Late answer for SQLite3 users: Now it is possible, but it implies a lot of writing if you have a lot of fields.

Instead of REPLACE you can use INSERT ... ON CONFLICT DO UPDATE:

INSERTINTO TEST (number, name) VALUES (52, 'John')
ON CONFLICT(number) DO UPDATESET number=excluded.number, name=excluded.name;

You then get your result:

1|23|Bill
2|52|John

But you need to specify, for each field, that you want to replace the value in the table by the value from the row which violates the unique constraint (called "excluded").

Unfortunately there is no way (to my knowledge) to update all the fields but one.