Insert or update sqlite3

Question

I'm facing a small problem here. I have a table with multiple columns but what i want to is write a sqlite query that to be used in my python script so that i can insert if the key is not found or else update other values if the key is already found. I researched a little and came through this answer but my concern in my content it's not working and keeps on adding the new rows even if it exists.

Using this i wrote a query since my update/insert is dependent on a column called as id which is a foreign key constraint to this table. Other two columns that i want to update are say name and role. so what i want to do is insert all three if the id is not found. if found then see if it has got same values or not for other two columns and update it. here is my attempt.

INSERT OR REPLACE INTO tblExample(id, name, role) VALUES ( COALESCE((SELECT id FROM tblExample WHERE id = 1),'1'),'name','role');

But it's still inserting new rows. why? how can i avoid it? any simpler solution?

Answer 1

A couple of things:

1. Duplicate detection is based on the PRIMARY KEY definition of the table you're inserting into. If you have not explicitly defined a primary key on the table, it will use the unique ROW_ID value (and you'll always get a new row). What is the PRIMARY KEY for this table?

2. INSERT OR REPLACE really does a replacement of the row, not an UPDATE. That means that if a duplicate is detected and the REPLACE pathway is chosen, any columns not given values in the statement will be updated to their DEFAULT values or to NULL if no DEFAULT is specified. In other words, if your table has an additional column called city, and a row is replaced, then city will be NULL, not whatever value it had in the original row.