Re: [sqlite] classic update join question

[Jim Morris]

  00
14Column 1 1 400
15ResultRow  3 2 000
16Next   2 9 000
17Next   0 6 001
18Close  0 0 000
19Close  1 0 000
20Close  2 0 000
21Halt   0 0 000
22Transaction0 0 000
23VerifyCookie   0 3 000
24TableLock  0 2 0 alpha  00
25TableLock  0 3 0 beta   00
26Goto   0 2 000
sqlite>

exactly the same except the update copy's the values or sets null if the seek 
fails, whereas the join returns the join of all the matching beta rows in the 
join instead of just the first result.

Of course, if you do not want to set alpha.frequency to null if there is no 
match, then you need:

sqlite> explain update alpha set frequency = coalesce((select frequency from beta 
where beta.term >= alpha.term), alpha.frequency);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 40000
2 Null   0 1 200
3 OpenRead   0 2 0 2  00
4 Rewind 0 8 000
5 Rowid  0 2 000
6 RowSetAdd  1 2 000
7 Next   0 5 001
8 Close  0 0 000
9 OpenWrite  0 2 0 2  00
10RowSetRead 1 38200
11NotExists  0 10200
12Null   0 3 400
13Null   0 5 000
14Integer1 6 000
15OpenRead   1 3 0 2  00
16OpenRead   2 4 0 keyinfo(1,BINARY)  00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1  00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid   2 8 000
23Seek   1 8 000
24Column 1 1 900
25Move   9 5 100
26IfZero 6 28-1   00
27Next   2 20000
28Close  1 0 000
29Close  2 0 000
30SCopy  5 3 000
31NotNull3 33000
32Column 0 0 300
33Column 0 1 400
34NotExists  0 35200
35MakeRecord 3 2 8 bb 00
36Insert 0 8 2 alpha  05
37Goto   0 10000
38Close  0 0 000
39Halt   0 0 000
40Transaction0 1 000
41VerifyCookie   0 3 000
42TableLock  0 2 1 alpha  00
43TableLock  0 3 0 beta   00
44Goto   0 2 000

With an index on beta.term it is O(n).  Without an index it would be O(n*m).
The straight join is O(n*m) with or without an index.

I don't think there is any magic.  It is simply the only way to implement the 
update expressed.

---
()  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 Yuriy Kaminskiy
Sent: Wednesday, 05 September, 2012 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] classic update join question

Igor Tandetnik 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?

Subquery: O(n*lo

Re: [sqlite] classic update join question

[Rob Richardson]

Many thanks to all of you who took the time to correct my misunderstanding of 
basic SQL.

I ran a little test in PostgreSQL (which is the quickest thing I have to play 
with), and of course, you are all correct and the query does work as designed.  
I was trying to figure out how to think about it, and I think I understand now. 
  In general, the database engine will walk through the target table,  evaluate 
the right side of the equal sign, and assign the result to the target column 
specified in the left side.  Simple.  I don't know why it didn't seem so simple 
yesterday.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Keith Medcalf]

00
16Next   2 9 000
17Next   0 6 001
18Close  0 0 000
19Close  1 0 000
20Close  2 0 000
21Halt   0 0 000
22Transaction0 0 000
23VerifyCookie   0 3 000
24TableLock  0 2 0 alpha  00
25TableLock  0 3 0 beta   00
26Goto   0 2 000
sqlite>

exactly the same except the update copy's the values or sets null if the seek 
fails, whereas the join returns the join of all the matching beta rows in the 
join instead of just the first result.

Of course, if you do not want to set alpha.frequency to null if there is no 
match, then you need:

sqlite> explain update alpha set frequency = coalesce((select frequency from 
beta where beta.term >= alpha.term), alpha.frequency);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000
1 Goto   0 40000
2 Null   0 1 200
3 OpenRead   0 2 0 2  00
4 Rewind 0 8 000
5 Rowid  0 2 000
6 RowSetAdd  1 2 000
7 Next   0 5 001
8 Close  0 0 000
9 OpenWrite  0 2 0 2  00
10RowSetRead 1 38200
11NotExists  0 10200
12Null   0 3 400
13Null   0 5 000
14Integer1 6 000
15OpenRead   1 3 0 2  00
16OpenRead   2 4 0 keyinfo(1,BINARY)  00
17Column 0 1 700
18IsNull 7 28000
19SeekGe 2 287 1  00
20Column 2 0 800
21IsNull 8 27000
22IdxRowid   2 8 000
23Seek   1 8 000
24Column 1 1 900
25Move   9 5 100
26IfZero 6 28-1   00
27Next   2 20000
28Close  1 0 000
29Close  2 0 000
30SCopy  5 3 000
31NotNull3 33000
32Column 0 0 300
33Column 0 1 400
34NotExists  0 35200
35MakeRecord 3 2 8 bb 00
36Insert 0 8 2 alpha  05
37Goto   0 10000
38Close  0 0 000
39Halt   0 0 000
40Transaction0 1 000
41VerifyCookie   0 3 000
42TableLock  0 2 1 alpha  00
43TableLock  0 3 0 beta   00
44Goto   0 2 000

With an index on beta.term it is O(n).  Without an index it would be O(n*m).
The straight join is O(n*m) with or without an index.

I don't think there is any magic.  It is simply the only way to implement the 
update expressed.

---
()  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 Yuriy Kaminskiy
> Sent: Wednesday, 05 September, 2012 19:53
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] classic update join question
> 
> Igor Tandetnik 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 impl

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 9:53 PM, Yuriy Kaminskiy wrote:

Igor Tandetnik 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?


Subquery: O(n*log(m)), join: O(n+m). Magic!


How does O(n+m) happen, exactly? A kind of tape merge algorithm, where 
two lists are traversed in order in parallel? Last time I looked closely 
at the output of EXPLAIN (which, I admit, was a few years ago), SQLite 
never attempted anything of the sort. Does it now?


Note also that, when n is much smaller than m (note that the OP 
mentioned 100K vs several million rows), O(n*log(m)) actually beats O(n+m).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Simon Slavin]

On 6 Sep 2012, at 2:53am, Yuriy Kaminskiy  wrote:

> Igor Tandetnik wrote:
>> On 9/5/2012 12:38 PM, E. Timothy Uy wrote:
>>> 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?
> 
> Subquery: O(n*log(m)), join: O(n+m). Magic!
> Of course, query optimizer sometimes can rewrite subquery as join (or 
> opposite),
> but I believe (unverified!) sqlite optimizer cannot do this currently.

You can all take a look at everything mentioning JOIN in

http://www.sqlite.org/optoverview.html

and at what EXPLAIN and EXPLAIN QUERY PLAN return.  JOINs are actually 
processed as something which resembles sub-selects.  The two constructions are 
more or less equivalent and there's no great speed difference between them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Yuriy Kaminskiy]

Igor Tandetnik 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?

Subquery: O(n*log(m)), join: O(n+m). Magic!
Of course, query optimizer sometimes can rewrite subquery as join (or opposite),
but I believe (unverified!) sqlite optimizer cannot do this currently.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Keith Medcalf]

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,
> > >
> > >

Re: [sqlite] classic update join question

[Keith Medcalf]

> 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
> > > perfor

Re: [sqlite] classic update join question

[Keith Medcalf]

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

Re: [sqlite] classic update join question

[Petite Abeille]

On Sep 5, 2012, at 11:00 PM, Igor Tandetnik  wrote:

>> And such statement should raise an exception if the scalar sub-query returns 
>> multiple rows, no?
> 
> Definitely not in SQLite. I don't believe it would do that in other DBMS 
> either, but won't bet on it. SQLite would take the value from the first row, 
> or NULL if there are no rows.

Oracle, for example, will make sure that a scalar query stays, hmmm, scalar. If 
not, it will raise an error [1]. Ditto for Postgres [2]. After all, what's the 
"first row" of a scalar sub-query without an explicit ordering? 'Random' is 
usually how it's called.

In my opinion, this is yet again one of these rather, hmmm, "weird" behaviors 
of SQLite. A bit along the line of that "unique" 'group by' behavior discussed 
in another thread somewhere else.

One person feature is another person bug.

[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions013.htm
[2] http://www.postgresql.org/docs/9.1/static/sql-expressions.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:54 PM, Petite Abeille wrote:


On Sep 5, 2012, at 10:38 PM, Igor Tandetnik  wrote:


A select statement that would most closely resemble your update statement would 
look like this:

select frequency, (select frequency from beta where beta.term = alpha.term)
from alpha;

This statement will always return exactly as many rows as there are rows in 
alpha table.


And such statement should raise an exception if the scalar sub-query returns 
multiple rows, no?


Definitely not in SQLite. I don't believe it would do that in other DBMS 
either, but won't bet on it. SQLite would take the value from the first 
row, or NULL if there are no rows.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Petite Abeille]

On Sep 5, 2012, at 10:38 PM, Igor Tandetnik  wrote:

> A select statement that would most closely resemble your update statement 
> would look like this:
> 
> select frequency, (select frequency from beta where beta.term = alpha.term)
> from alpha;
> 
> This statement will always return exactly as many rows as there are rows in 
> alpha table.

And such statement should raise an exception if the scalar sub-query returns 
multiple rows, no?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:35 PM, Igor Tandetnik wrote:

On 9/5/2012 4:32 PM, E. Timothy Uy wrote:

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term


There can't be multiple hits, or even a single hit. This statement will
not run, as it's not syntactically valid.


A select statement that would most closely resemble your update 
statement would look like this:


select frequency, (select frequency from beta where beta.term = alpha.term)
from alpha;

This statement will always return exactly as many rows as there are rows 
in alpha table.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:32 PM, E. Timothy Uy wrote:

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term


There can't be multiple hits, or even a single hit. This statement will 
not run, as it's not syntactically valid.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[E. Timothy Uy]

Igor, what happens if there are multiple hits for

SELECT frequency FROM beta WHERE beta.term
= alpha.term

(there aren't but, it is possible for there to be).  Rob could be right in
a sense.

On Wed, Sep 5, 2012 at 1:28 PM, Igor Tandetnik  wrote:

> On 9/5/2012 4:20 PM, Rob Richardson wrote:
>
>> Well, I think you want a where clause on your main UPDATE query.  What
>> you wrote will set the frequency of every record in the alpha table to the
>> value from the beta table, for every record in the beta table that matches
>> an alpha record.  (It's late, I'm tired and that's incoherent.  I hope you
>> followed it.)  If you have 1000 records in the two tables that match each
>> other, every record in the alpha table will be updated 1000 times.
>>
>
> No, that's not true. Every record in alpha will be updated exactly once.
> --
> Igor Tandetnik
>
>
> __**_
> 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

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:20 PM, Rob Richardson wrote:

Well, I think you want a where clause on your main UPDATE query.  What you 
wrote will set the frequency of every record in the alpha table to the value 
from the beta table, for every record in the beta table that matches an alpha 
record.  (It's late, I'm tired and that's incoherent.  I hope you followed it.) 
 If you have 1000 records in the two tables that match each other, every record 
in the alpha table will be updated 1000 times.


No, that's not true. Every record in alpha will be updated exactly once.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Rob Richardson]

Well, I think you want a where clause on your main UPDATE query.  What you 
wrote will set the frequency of every record in the alpha table to the value 
from the beta table, for every record in the beta table that matches an alpha 
record.  (It's late, I'm tired and that's incoherent.  I hope you followed it.) 
 If you have 1000 records in the two tables that match each other, every record 
in the alpha table will be updated 1000 times.

Or am I merely demonstrating my ignorance?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of E. Timothy Uy
Sent: Wednesday, September 05, 2012 4:11 PM
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... ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

On 9/5/2012 4:11 PM, E. Timothy Uy wrote:

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)?


I don't know what other DBMS are doing. In SQLite, this update statement 
would have a query plan very similar to the one for


select alpha.frequency, beta.frequency from alpha, beta
where beta.term = alpha.term;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[E. Timothy Uy]

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  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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Igor Tandetnik]

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

[sqlite] classic update join question

[E. Timothy Uy]

Hi sqlite-users,

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)


or we can create a temp table with the joined data and then drop and
replace alpha. How inefficient is the former method? Is it "ok"?  In this
case, alpha is about 100k rows and beta might be several million. The
'term' is indexed. Will the database really be doing a select in beta for
every single line in alpha?

Respectfully,
Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users