Re: [sqlite] Update vs Insert
Barton Torbert wrote: > 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. No database I've ever heard of does that. It must be a feature of the library you are using. Contact its authros for support. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update vs Insert
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users->boun...@sqlite.org] On Behalf Of Barton Torbert >Sent: Tuesday, September 22, 2009 11:07 AM >To: General Discussion of SQLite Database >Subject: [sqlite] Update vs Insert > >Hello, > >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. > >Is the SQLite update restricted to a record you know already exists? > > > >Bart 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. Logan Ratner | Software Engineer | Gas Chromatographs Emerson Process Management | 5650 Brittmoore Rd | Houston | TX | 77041 | USA T +1 713 839 9656 | F +1 713 827 3807 logan.rat...@emerson.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update vs Insert
>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. (Er, that should read 'not just SQLite.') Logan Ratner | Software Engineer | Gas Chromatographs Emerson Process Management | 5650 Brittmoore Rd | Houston | TX | 77041 | USA T +1 713 839 9656 | F +1 713 827 3807 logan.rat...@emerson.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update vs Insert
On 22 Sep 2009, at 5:06pm, Barton Torbert wrote: > I am having problems with the update command (through the interface > library). Which interface library ? The sqlite3_ C calls ? Or a library or API for some other language ? > 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. > > Is the SQLite update restricted to a record you know already exists? Not sure what you mean. In SQL the UPDATE command has an optional WHERE clause that specifies which /records/ to update. You don't update a specific record, your command may make changes to zero, one or more records depending on how many records satisfy the clause. Updating zero records is not an error, it's simply a command that has no noticeable effect. What sort of command are you trying, and what error results from it ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update vs Insert
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
