Re: Re[2]: [sqlite] Fastest way to check if new row or update existing one?
On Mon, 31 Dec 2007 13:54:50 +, "Hugo Ferreira" <[EMAIL PROTECTED]> wrote: >Hmmm... Would it be possible to make a trigger on a >table such that if any update fails, it does an insert? >If so, then one would only need to issue updates. I don't think that would be possible, an update trigger most likely wouldn't fire on a failed update. The only thing you can rely on is: zero rows changed. But you could try it anyway. Experiments are always worth the effort. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Fastest way to check if new row or update existing one?
Did you try using INSERT OR REPLACE? Hugo Ferreira wrote: > Hmmm... Would it be possible to make a trigger on a table such that if any > update fails, it does an insert? > If so, then one would only need to issue updates. > > On Dec 26, 2007 11:35 AM, Kees Nuyt <[EMAIL PROTECTED]> wrote: > >> On Wed, 26 Dec 2007 10:17:43 +0200, Ion Silvestru >> <[EMAIL PROTECTED]> wrote: >> > QUESTION: is there a better way to make this important decision? using > Sqlite INSERT OR REPLACE may work for you. >>> There is a problem with "INSERT OR REPLACE" in that "REPLACE" is not >>> truly , but is (existing row is deleted and >>> new row is added), or I am wrong? >> It is a full replacement of the row, just as the word REPLACE >> (Take the place or moveinto the position of) suggests. Perhaps >> you are confused with UPDATE. >> REPLACE has been introduced to increase compatibility with other >> database engines, they all follow the same strategy. >> >>> If I am correct, then a question to developers of SQLite: >>> >>> Is it difficult to change the behaviour of the "REPLACE" part of "INSERT >> OR REPLACE" >>> to be the correct behaviour, row content is updated, and not deleted then >> inserted? >> >> What would be the difference? >> I guess your new row doesn't provide some of the column values >> of the existing row, and you want to keep some of those. In that >> case, SELECT / UPDATE is the only option. >> >> To change the behaviour of REPLACE into selective updating of >> columns SQLite would have to know which columns it would have to >> update and which not. It simply can't. >> >> A nice solution is found in >> Date: Tue, 24 Apr 2007 14:36:48 -0400 >> Subject: [sqlite] Re: INSERT OR REPLACE without new rowid , >> Message-ID: <[EMAIL PROTECTED]> : >> >> IT> You can do >> IT> >> IT> UPDATE ... WHERE keyfield='xxx'; >> IT> >> IT> then use sqlite3_changes to see whether any update >> IT> has in fact taken place, and run INSERT if not. >> IT> >> IT> Igor Tandetnik >> >>> Thanks in advance and happy hollidays! >> HTH >> -- >> ( Kees Nuyt >> ) >> c[_] >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Fastest way to check if new row or update existing one?
On Wed, 26 Dec 2007 10:17:43 +0200, Ion Silvestru <[EMAIL PROTECTED]> wrote: >>>QUESTION: is there a better way to make this important decision? using >>>Sqlite > >>INSERT OR REPLACE may work for you. > >There is a problem with "INSERT OR REPLACE" in that "REPLACE" is not >truly , but is (existing row is deleted and >new row is added), or I am wrong? It is a full replacement of the row, just as the word REPLACE (Take the place or move into the position of) suggests. Perhaps you are confused with UPDATE. REPLACE has been introduced to increase compatibility with other database engines, they all follow the same strategy. >If I am correct, then a question to developers of SQLite: > >Is it difficult to change the behaviour of the "REPLACE" part of "INSERT OR >REPLACE" >to be the correct behaviour, row content is updated, and not deleted then >inserted? What would be the difference? I guess your new row doesn't provide some of the column values of the existing row, and you want to keep some of those. In that case, SELECT / UPDATE is the only option. To change the behaviour of REPLACE into selective updating of columns SQLite would have to know which columns it would have to update and which not. It simply can't. A nice solution is found in Date: Tue, 24 Apr 2007 14:36:48 -0400 Subject: [sqlite] Re: INSERT OR REPLACE without new rowid , Message-ID: <[EMAIL PROTECTED]> : IT> You can do IT> IT> UPDATE ... WHERE keyfield='xxx'; IT> IT> then use sqlite3_changes to see whether any update IT> has in fact taken place, and run INSERT if not. IT> IT> Igor Tandetnik >Thanks in advance and happy hollidays! HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Fastest way to check if new row or update existing one?
>>QUESTION: is there a better way to make this important decision? using >>Sqlite >INSERT OR REPLACE may work for you. There is a problem with "INSERT OR REPLACE" in that "REPLACE" is not truly , but is (existing row is deleted and new row is added), or I am wrong? If I am correct, then a question to developers of SQLite: Is it difficult to change the behaviour of the "REPLACE" part of "INSERT OR REPLACE" to be the correct behaviour, row content is updated, and not deleted then inserted? Thanks in advance and happy hollidays! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Fastest way to check if new row or update existing one?
On Tue, 25 Dec 2007 15:07:12 +0100, "Mag. Wilhelm Braun" <[EMAIL PROTECTED]> wrote: >hi, > >just a short question to speed up: > >as with any database one has quite often to decide if we *INSERT a NEW >row -- or -- UPDATE an existing row* > > >at the moment I do a check select on an unique ID intege which is >resonable fast: > >code: > > > SELECT ID FROM MyTable WHERE Account='MyAccountName' > > > >I just fetch one row. > >if that get's a return I update otherwise I insert a new row. > >QUESTION: is there a better way to make this important decision? using >Sqlite > >regards W.Braun INSERT OR REPLACE may work for you. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_replace.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Fastest way to check if new row or update existing one?
hi, just a short question to speed up: as with any database one has quite often to decide if we *INSERT a NEW row -- or -- UPDATE an existing row* at the moment I do a check select on an unique ID intege which is resonable fast: code: SELECT ID FROM MyTable WHERE Account='MyAccountName' I just fetch one row. if that get's a return I update otherwise I insert a new row. QUESTION: is there a better way to make this important decision? using Sqlite regards W.Braun
