CODEWITHSUNDEEP
sqlite
birka
birka

Reputation: 55

Sqlite INSERT OR REPLACE is incrementing rowid

I am using the INSERT OR REPLACE syntax to either insert or update a row that may exist based on a unique column constraint within the table.

The table structure is

| Name | Age | CustomerId (unique) |

When I print out the table the first time, I get something like

| Name | Age | CustomerId | rowid |
|"Bob" | 22  | 5          | 1     |

Then I run INSERT OR REPLACE INTO MyTable(Name, Age, CustomerId) VALUES ("Bob", 23, 5);"

Without fail, this will increment the rowid column each time it is run. So now the result is

| Name | Age | CustomerId | rowid |
|"Bob" | 23  | 5          | 2     |

How do I prevent rowid from incrementing?

Upvotes: 1

Views: 833

Answers (1)

forpas
forpas

Reputation: 164099

This is how INSERT OR REPLACE works.
If a violation of a unique index/constraint occurs then the existing row is deleted and the new row is inserted, so a new rowid is assigned for the new row.

If you don't want this to happen you must use UPSERT:

insert into tablename(Name, Age, CustomerId) values ('Bob', 23, 5)
on conflict(CustomerId) do update 
set Name = excluded.Name,
    Age = excluded.Age;

See the demo.

Upvotes: 2

Related Questions