> 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
