I like replying to myself, obviously ... Update alpha Set alpha.value = (select beta.value from beta where beta.key = alpha.key) Where exists (select * from beta where beta.key = alpha.key)
Could be more efficiently: Update alpha Set alpha.value = coalesce((select beta.value from beta where beta.key = alpha.key), alpha.value) Which is probably what query re-write would actually execute ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: Keith Medcalf [mailto:kmedc...@dessus.com] > Sent: Wednesday, 05 September, 2012 18:18 > To: 'General Discussion of SQLite Database' > Subject: RE: [sqlite] classic update join question > > > Update alpha > > Set alpha.value = beta.value > > From alpha, beta > > Where alpha.key = beta.key > > > > (which is how you would express an update of a join table in other DBMS's) > > > > Is equivalent to (and will be executed as) > > > > Update alpha > > Set alpha.value = (select beta.value from beta where beta.key = > alpha.key) > Where exists (select * from beta where beta.key = alpha.key) > > Is the correct translation. Without it, alpha.value will be set to null > where there is no matching beta row ... > > Mutatis mutandis the second query also for the same reason. > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org > > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Keith Medcalf > > Sent: Wednesday, 05 September, 2012 18:09 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] classic update join question > > > > > > I think what you are asking is whether an update through a cursor: > > > > Update alpha > > Set alpha.value = beta.value > > From alpha, beta > > Where alpha.key = beta.key > > > > (which is how you would express an update of a join table in other DBMS's) > > > > Is equivalent to (and will be executed as) > > > > Update alpha > > Set alpha.value = (select beta.value from beta where beta.key = > alpha.key) > > > > And the answer is yes, if and only if beta.key is constrained unique. Not > > only are they equivalent, they are executed in exactly the same fashion. > If > > beta.key is not constrained unique (or the correlation condition is not an > > equality) then the results are entirely different depending on whether the > > first joined update is executed by a cursor or by query-rewrite to the > > correlated form. If it is executed through a cursor, then the alpha.value > > will be the last match. If it is executed as a correlated subquery, the > > value will be the first-match. > > > > If beta.key is not constrained unique, then both forms are algebraically > > indeterminate. > > > > > > > > So for example while if you did something like: > > > > Update alpha > > Set alpha.value = beta.value > > From alpha, beta > > Where beta.key => alpha.key > > > > will not return (have the same effect) as > > > > update alpha > > set alpha.value = (select beta.value from beta where beta.key => > > alpha.key) > > > > because the first update will set the alpha.value to the beta.value > > corresponding to the "largest" beta.key => alpha.key (the last match > > scanned), while the second will set alpha.value to the beta.value > > corresponding to the "smallest" beta.key => alpha.key (ie, the first one > > found). > > > > For the former form, the value of alpha.value will be updated for each > > beta.value match. For the latter correlated scaler subquery, each > > alpha.value will only be updated once. > > > > > > > > If beta.key is not indexed performance will be "crappy" to say the least > > because rather than doing an index seek to find the beta.value, it will do > a > > table scan for each row in alpha... > > > > > > --- > > () ascii ribbon campaign against html e-mail > > /\ www.asciiribbon.org > > > > > -----Original Message----- > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > > boun...@sqlite.org] On Behalf Of E. Timothy Uy > > > Sent: Wednesday, 05 September, 2012 14:11 > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] classic update join question > > > > > > Dear Igor, > > > > > > Are you saying that > > > > > > UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term > > > = alpha.term) > > > > > > is just as efficient as it gets and equivalent to an update using join > (in > > > other dbs)? That would helpful to know. I do kind of imagine some kind of > > > black magic... ;) > > > > > > > > > On Wed, Sep 5, 2012 at 12:24 PM, Igor Tandetnik <itandet...@mvps.org> > > wrote: > > > > > > > On 9/5/2012 12:38 PM, E. Timothy Uy wrote: > > > > > > > >> I have a column in table 'alpha' which I would like to populate with > > data > > > >> from table 'beta'. As far as I know, we cannot do an UPDATE using JOIN > > in > > > >> sqlite, but we can > > > >> > > > >> UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE > beta.term > > = > > > >> > > > >>> alpha.term) > > > >>> > > > >> > > > >> Will the database really be doing a select in beta for > > > >> every single line in alpha? > > > >> > > > > > > > > Yes - same as when implementing a join. How do you think a join is > > > > performed - black magic? > > > > -- > > > > Igor Tandetnik > > > > > > > > ______________________________**_________________ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite- > > > **users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > > > > > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
