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 are updated from the a and b of the same record. (Or
it's supposed to be that way, ignoring any syntax errors from above.)
Does SQLite really do two lookups? Is there a more efficient way of doing this?
I believe SQLite does technically perform two lookups, but the second
one is likely to be must faster than the first as the row is already
present in the cache.
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, 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 disease.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
