I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite.
I have a table defined as follows:
CREATE TABLE Book ID INTEGER PRIMARY KEY AUTOINCREMENT, Name VARCHAR(60) UNIQUE, TypeID INTEGER, Level INTEGER, Seen INTEGER
What I want to do is add a record with a unique Name. If the Name already exists, I want to modify the fields.
Can somebody tell me how to do this please?
Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first. Unfortunately, this the 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.
If the row being inserted already exists, INSERT OR UPDATE performs an UPDATE operation, updating the row with the specified column values. An update occurs even when the specified data values are identical to the existing data.
Have a look at http://sqlite.org/lang_conflict.html.
You want something like:
insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = "SearchName"), "SearchName", ...);
Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why there's a subselect for the ID
column: In the replacement case the statement would set it to NULL and then a fresh ID would be allocated.
This approach can also be used if you want to leave particular field values alone if the row in the replacement case but set the field to NULL in the insert case.
For example, assuming you want to leave Seen
alone:
insert or replace into Book (ID, Name, TypeID, Level, Seen) values ( (select ID from Book where Name = "SearchName"), "SearchName", 5, 6, (select Seen from Book where Name = "SearchName"));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With