On 10/11/2010, at 7:19 PM, Michele Pradella wrote: > In-Reply-To: <4cda28ea.5030...@gmail.com>
Firstly, please start a post to this mail list as a new message, not a reply to a previous unrelated message. That's known as "thread hijacking" and confuses discussions. > Hi all, I have to INSERT a row in a DB but I have first to check if the Key > I'm inserting already exist. Good question. > Now I'm doing a "SELECT count..." first to check if the key exist and then > INSERT or UPDATE records. I can see two problems with this approach: 1. It sounds like you're executing an SQL select, then dropping out of SQL to perform a logic test on the result, then executing a new SQL statement depending on the logic result. It's more efficient and reliable to do it all in SQL. 2. Using "select count" will test the entire table, even if the key exists in the first row it tests. You should use "in" or "exists" instead of "count". The hit is reduced if the column is indexed, but it's still poor SQL. > Do you know if there's a better or faster way to do that? You could: begin immediate ; update MyTable set Detail = newDetail where ID = newID ; insert into MyTable(ID, Detail) select newID, newDetail where newID not in (select ID from MyTable) ; commit ; It may be faster to change the insert line to: insert into MyTable(ID, Detail) select newID, newDetail where changes() > 0 and newID not in (select ID from MyTable) but I don't know, since I haven't really played with changes(). Alternatively, you could use "insert or ignore" or "insert or replace" depending on what you want to do if the key exists. Note that "insert or replace" will actually delete the old row and insert a new one, something that can make a mess if, for instance, you wanted to keep the value of other columns or you have internal database logic that is initiated by deletes and inserts, so I tend to avoid it. HTH, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
