Re: [sqlite] updating records in table A from joined recordsintable B

2011-04-01 Thread Robert Poor
On Fri, Apr 1, 2011 at 21:36, Igor Tandetnik wrote: > update table_a set avalue = ( >    select bvalue from table_b where akey=bkey >    union all >    select avalue); That also works. But at the risk of "moving the finish line during the race", I should point out that the original question was

Re: [sqlite] updating records in table A from joined recordsintable B

2011-04-01 Thread Igor Tandetnik
Robert Poor wrote: > On Fri, Apr 1, 2011 at 20:13, Igor Tandetnik wrote: >> Or >> update table_a set avalue=coalesce( >> (select bvalue from table_b where akey=bkey), avalue); > > Nice. But if table_b.value is null, it won't get copied into table_a. > That would be a unexpected. update table_

Re: [sqlite] updating records in table A from joined records intable B

2011-04-01 Thread Robert Poor
On Fri, Apr 1, 2011 at 20:13, Igor Tandetnik wrote: > Or > update table_a set avalue=coalesce( >    (select bvalue from table_b where akey=bkey), avalue); Nice. But if table_b.value is null, it won't get copied into table_a. That would be a unexpected. __

Re: [sqlite] updating records in table A from joined records intable B

2011-04-01 Thread Igor Tandetnik
David Garfield wrote: > UPDATE table_a SET avalue=(SELECT bvalue FROM table_b WHERE akey=bkey) > WHERE EXISTS(SELECT 1 FROM table_b WHERE akey=bkey); Or update table_a set avalue=coalesce( (select bvalue from table_b where akey=bkey), avalue); -- Igor Tandetnik ___

Re: [sqlite] updating records in table A from joined records in table B

2011-04-01 Thread Robert Poor
@Pavel: close. @David: that works. = DROP TABLE IF EXISTS `table_a`; CREATE TABLE `table_a` (`key` int(11), `value` float, `str` varchar(255)); DROP TABLE IF EXISTS `table_b`; CREATE TABLE `table_b` (`key` int(11), `value` float, `str` varchar(255)); INSERT INTO `t

Re: [sqlite] updating records in table A from joined records in table B

2011-04-01 Thread Pavel Ivanov
> ... that is to say, update table_a.value from table_b.value, but only > on rows where table_a.key = table_b.key update table_a set value = (select table_b.value from table_b where table_b.key = table_a.key) Pavel On Fri, Apr 1, 2011 at 8:42 PM, Robert Poor wrote: > I'd like to be able to up

Re: [sqlite] updating records in table A from joined records in table B

2011-04-01 Thread David Garfield
A bit redundant, but how about: CREATE TABLE table_a (akey integer, avalue float); CREATE TABLE table_b (bkey integer, bvalue float); INSERT INTO table_a (akey, avalue) VALUES (1, 1.0); INSERT INTO table_a (akey, avalue) VALUES (2, 2.0); INSERT INTO table_a (akey, avalue) VALUES (3, 3.0); IN

[sqlite] updating records in table A from joined records in table B

2011-04-01 Thread Robert Poor
I'd like to be able to update specific records in table A from joined records in table B. So for example: CREATE TABLE "table_a" ("key" integer, "value" float); CREATE TABLE "table_b" ("key" integer, "value" float); INSERT INTO "table_a" ("key", "value") VALUES (1, 1.0), (2, 2.0),(3, 3.0); I

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

2011-04-01 Thread Ulrich Telle
Am 01.04.2011 15:44, schrieb Mr. Puneet Kishor: >> See http://www.sqlite.org/contrib extension-functions.c > > Is there a guide on how to compile the above extension-functions.c > into sqlite, so it is always available without have to do load the > extension explicitly? > > Pointers appreciated.

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

2011-04-01 Thread Mr. Puneet Kishor
On Mar 31, 2011, at 2:28 PM, Doug Currie wrote: > On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote: > >> I have tried the latest Explorer and it gets a syntax error on STDEV. >> However, that function works in Eclipse just fine, to take the standard >> deviation of a column (like min, max, av