On Tue, Sep 22, 2009 at 11:31:16AM -0500, logan.rat...@emerson.com scratched on the wall:
> > I am having problems with the update command (through the interface > > library). In most databases if you issue an update to a record that > > does not exist, it simply adds a new record. When I try this in SQLite > > I get an error back. > You can't do that with an 'update' but you can with 'insert or replace.' > This is true in any SQL driven database, not just SQL. Errr... no. IIRC, "REPLACE" is an MySQL-ism, and "INSERT OR REPLACE" is specific to SQLite. SQLite also accepts just "REPLACE" to mean "INSERT OR REPLACE" because of the MySQL syntax. As I understand, no other major database supports the "REPLACE" concept (other than as a string manipulation function). It should also be pointed out (again) that the command is INSERT OR REPLACE and *not* INSERT OR UPDATE. Using REPLACE in SQLite basically adds a "ON CONFLICT DELETE" clause, meaning that *any* row that would trigger a unique constraint conflict is first deleted, then the row is inserted as normal. This means the old row(s) is lost, so you can't do a partial row update. It also means that if you are not providing an explicit ROWID value, the row will get a new one (which can be bad for keys). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
