RE: [sqlite] Update Columns in One Table Using Values From Another Table
Hrmm... I wonder if this would work (complete guess, totally untested) INSERT OR REPLACE INTO core SELECT Core.A, Updates.B, Core.C, Updates.D FROM Core INNER JOIN Updates ON (Core.A = Updates.A) Idea being, I guess, to get the rows that you ultimately want from the sub-select and then use insert or replace to get them into the table. -T > -Original Message- > From: Chris Peachment [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 2:15 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Update Columns in One Table Using > Values From Another Table > > On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > > >Hi Chris, > > >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > > >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >> > >>>Chris Peachment wrote: > >>>> I have a database with more than 200,000 records in the > >>>> core table. An update table of similar record count contains > >>>> a proper subset of the core table columns. > >>>> > >>>> I'm looking for a fast method of merging the values in the > >>>> two tables such that : > >>>> > >>>> 1. core table columns are updated, and > >>>> 2. non-existent core records are inserted from the update table. > >>>> > >>>Will INSERT OR REPLACE do what you want? > >> > >> > >>>Gerry > >> > >> > >>Regrettably no. When an existing core record is found then it > >>is deleted before the insert. That means that all columns are > >>given new values and not just the ones to be updated. > > >That is exactly what INSERT OR REPLACE does. > > >http://www.sqlite.org/lang_insert.html > >http://www.sqlite.org/lang_conflict.html > > > Sorry for the confusion I introduced. I know the behaviour > of INSERT OR REPLACE is as-described, and that is NOT > what I want. I need to keep the non-updated columns. > > Chris > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 14:15:00 -0400, you wrote: >On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > >>Hi Chris, > >>On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > >>>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >>> Will INSERT OR REPLACE do what you want? >>> Gerry >>> >>>Regrettably no. When an existing core record is found then it >>>is deleted before the insert. That means that all columns are >>>given new values and not just the ones to be updated. > >>That is exactly what INSERT OR REPLACE does. > >>http://www.sqlite.org/lang_insert.html >>http://www.sqlite.org/lang_conflict.html > > >Sorry for the confusion I introduced. I know the behaviour >of INSERT OR REPLACE is as-described, and that is NOT >what I want. I need to keep the non-updated columns. > >Chris Oops, I obviously misread your statement. Just a suggestion (no time to try it myself): Perhaps a BEFORE INSERT trigger on Core can help, triggered by an INSERT ... SELECT ... FROM UpdateTable? I'm not sure if it would work and how fast it would be. Good luck! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: >Hi Chris, >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >> >>>Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. >>>Will INSERT OR REPLACE do what you want? >> >> >>>Gerry >> >> >>Regrettably no. When an existing core record is found then it >>is deleted before the insert. That means that all columns are >>given new values and not just the ones to be updated. >That is exactly what INSERT OR REPLACE does. >http://www.sqlite.org/lang_insert.html >http://www.sqlite.org/lang_conflict.html Sorry for the confusion I introduced. I know the behaviour of INSERT OR REPLACE is as-described, and that is NOT what I want. I need to keep the non-updated columns. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Chris Peachment wrote: On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. Will INSERT OR REPLACE do what you want? Gerry Regrettably no. When an existing core record is found then it is deleted before the insert. That means that all columns are given new values and not just the ones to be updated. Chris You do have to specify all the columns, but you can set the unchanging columns to what they already are. I don't remember the exact syntax, but it can be done. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Hi Chris, On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >>Chris Peachment wrote: >>> I have a database with more than 200,000 records in the >>> core table. An update table of similar record count contains >>> a proper subset of the core table columns. >>> >>> I'm looking for a fast method of merging the values in the >>> two tables such that : >>> >>> 1. core table columns are updated, and >>> 2. non-existent core records are inserted from the update table. >>> >>Will INSERT OR REPLACE do what you want? > > >>Gerry > > >Regrettably no. When an existing core record is found then it >is deleted before the insert. That means that all columns are >given new values and not just the ones to be updated. That is exactly what INSERT OR REPLACE does. http://www.sqlite.org/lang_insert.html http://www.sqlite.org/lang_conflict.html >Chris -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >Chris Peachment wrote: >> I have a database with more than 200,000 records in the >> core table. An update table of similar record count contains >> a proper subset of the core table columns. >> >> I'm looking for a fast method of merging the values in the >> two tables such that : >> >> 1. core table columns are updated, and >> 2. non-existent core records are inserted from the update table. >> >Will INSERT OR REPLACE do what you want? >Gerry Regrettably no. When an existing core record is found then it is deleted before the insert. That means that all columns are given new values and not just the ones to be updated. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
Chris Peachment wrote: I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. Will INSERT OR REPLACE do what you want? Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
