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
