CODEWITHSUNDEEP
insertsqlite
user9517536248
user9517536248

Reputation: 355

Insert or update sqlite3

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?

Upvotes: 3

Views: 1624

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 51008

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.

Upvotes: 1

dwxw
dwxw

Reputation: 1099

That should work, but the trick is to make sure your id, which I assume is the primary key of this table, matches an existing id, else it won't see it as a duplicate record.

Upvotes: 0

Related Questions