Re: [sqlite] classic update join question

2012-09-06 Thread Jim Morris
sers- 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

Re: [sqlite] classic update join question

2012-09-06 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread Keith Medcalf
date 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread Keith Medcalf
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 w

Re: [sqlite] classic update join question

2012-09-05 Thread Keith Medcalf
on 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: Ge

Re: [sqlite] classic update join question

2012-09-05 Thread Keith Medcalf
iginal 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 > > D

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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)

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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,

Re: [sqlite] classic update join question

2012-09-05 Thread Rob Richardson
] 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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

Re: [sqlite] classic update join question

2012-09-05 Thread 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,

Re: [sqlite] classic update join question

2012-09-05 Thread 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 =

[sqlite] classic update join question

2012-09-05 Thread 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