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
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 mlal...@outsitenetworks.com wrote:
From: Marc L. Allen mlal...@outsitenetworks.com
Subject: [sqlite] How efficient is this?
To: sqlite-users@sqlite.org sqlite
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
: 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 mlal...@outsitenetworks.com wrote:
From: Marc L. Allen mlal...@outsitenetworks.com
Subject: [sqlite] How
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
On Thu, Mar 8, 2012 at 10:43 AM, Igor Tandetnik itandet...@mvps.org 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
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 than the
On Thu, Mar 8, 2012 at 11:22 AM, Nico Williams n...@cryptonector.com 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
On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
mlal...@outsitenetworks.com 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
On Thu, Mar 8, 2012 at 9:53 AM, Marc L. Allen
mlal...@outsitenetworks.com 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
On Thu, Mar 8, 2012 at 11:56 AM, Nico Williams n...@cryptonector.com wrote:
On Thu, Mar 8, 2012 at 11:47 AM, Marc L. Allen
mlal...@outsitenetworks.com wrote:
But this is extremely inefficient as well. Since each record is, in fact,
an update, you're actually performing a delete/insert for
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 inefficient if the
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
On Thu, Mar 8, 2012 at 12:00 PM, Marc L. Allen
mlal...@outsitenetworks.com 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
14 matches
Mail list logo
