On Thu, Mar 8, 2012 at 12:00 PM, Marc L. Allen
wrote:
>> This particular query need not be very inefficient if the pages needed
>> to do the second sub-query are left in the cache from the first sub-
>> query...
>
> No. That's true. But this is just a simple example. It's possible that the
> s
On 3/8/2012 1:00 PM, Marc L. Allen wrote:
By the way, the syntax diagram doesn't allow "+=" so how would the above be
coded in reality?
SET a = a + whatever
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:80
> > I'm trying to do a multi-column update, adding values from another
> table. Something like:
> >
> > UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
> > b += (SELECT b FROM t2 WHERE t1.id =
> > t2.id);
>
> This particular query need not be very inefficie
On Thu, Mar 8, 2012 at 11:56 AM, Nico Williams wrote:
> On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
> wrote:
>> But this is extremely inefficient as well. Since each record is, in fact,
>> an update, you're actually performing a delete/insert for each record,
>> activating any recursive tri
On Thu, Mar 8, 2012 at 9:53 AM, Marc L. Allen
wrote:
> I'm trying to do a multi-column update, adding values from another table.
> Something like:
>
> UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
> b += (SELECT b FROM t2 WHERE t1.id = t2.id);
This part
On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
wrote:
> But this is extremely inefficient as well. Since each record is, in fact, an
> update, you're actually performing a delete/insert for each record,
> activating any recursive triggers you have as well. On top of that, any
> UPDATE trigger
On Thu, Mar 8, 2012 at 11:22 AM, Nico Williams wrote:
> I use this all the time as it's the only way in SQLite3 to do UPDATEs
> with JOINs. I use it with an INTEGER PRIMARY KEY column. SQLite3
> tables always have a rowid somehow that's suitable for this, but it's
> not a good assumption to bake
> > insert or replace into t1(id, a, b, otherColumns) select t1.id, t1.a
> +
> > t2.a, t1.b + t2.b, t1.otherColumns from t1 left join t2 on (t1.id =
> > t2.id);
> >
> > (this assumes id column is declared unique, or primary key; replace
> > with ROWID otherwise). I suspect this cure might be worse
On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik wrote:
> Unfortunately, there are no good alternatives for this statement. I wish
> SQLite supported UPDATE ... FROM syntax (non-standard, used by some other
> DBMS). Here's one not-so-good alternative:
>
> insert or replace into t1(id, a, b, otherCo
On 3/8/2012 10:53 AM, Marc L. Allen wrote:
I'm trying to do a multi-column update, adding values from another table.
Something like:
UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
b += (SELECT b FROM t2 WHERE t1.id = t2.id);
Note that both a and b ar
ussion of SQLite Database
> Subject: Re: [sqlite] How efficient is this?
>
>
> you can do:
> update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)
>
> just like oracle do
>
>
>
>
> --- On Thu, 3/8/12, Marc L. Allen wrote:
>
> > From: Marc
On 3/8/2012 11:42 AM, Sebastian Bermudez wrote:
you can do:
update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)
just like oracle do
You can't - SQLite doesn't accept this syntax.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-use
you can do:
update t1 set (a,b)=(select a, b from t2 where t1.id=t2.id)
just like oracle do
--- On Thu, 3/8/12, Marc L. Allen wrote:
> From: Marc L. Allen
> Subject: [sqlite] How efficient is this?
> To: "sqlite-users@sqlite.org"
> Date: Thursday, March 8, 2012,
I'm trying to do a multi-column update, adding values from another table.
Something like:
UPDATE t1 SET a += (SELECT a FROM t2 WHERE t1.id = t2.id),
b += (SELECT b FROM t2 WHERE t1.id = t2.id);
Note that both a and b are updated from the a and b of the same record.
14 matches
Mail list logo
