Please reply to the list, so that others may take part in the conversation, and so that others with similar future questions can search out the answers in the mailing list archive.
On Mon, Oct 18, 2010 at 02:55:44PM +0100, Ian Hardingham scratched on the wall: > Thanks Jay. > > A slightly related question. I'm often needing to do the following thing: > > SELECT something > If rows returned > 0 > add a row. > otherwise > do nothing > > Is there a single SQLite command for this kind of thing? If you have a unique column (or set of columns) that can be used to target a specific row (e.g. whatever you're putting into the SELECT's WHERE clause) you can just attempt the INSERT, and set things up to fail if a row with that unique column value already exists: INSERT OR IGNORE INTO... That depends on a unique key, however. If you have no unique constraint (e.g. the SELECT may return >1 rows) then what you're doing is about the only way to do it. BTW, if you do use a SELECT followed by program logic to do (or not do) the INSERT, make sure you wrap the whole process in a transaction. Without the transaction, it is possible for the state of the database to change between the SELECT and the INSERT (e.g. some other connection might make the same insertion).. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
