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

[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 how to
update entire records, not individual fields.  If you wanted to set
more than one column, this construct would get ugly quickly.  So
imagine that our setup was as follows:
==
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 `table_a` (`key`, `value`, `str`) VALUES (1,   1.0, 'mon');
INSERT INTO `table_a` (`key`, `value`, `str`) VALUES (2,   2.0, 'tue');
INSERT INTO `table_a` (`key`, `value`, `str`) VALUES (3,   3.0, 'wed');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (1, 101.0, 'apr');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (2, 102.0, 'may');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (4, 104.0, 'jun');
==
I'm looking for a construct that joins on the 'key' field, but updates
both the 'value' and 'str' fields, with the result:

select * from table_a;
1 | 101.0 | 'apr'
2 | 102.0 | 'may'
3 |3.0 | 'wed'

(FWIW, both PostgreSQL and MySQL have extensions to UPDATE that make
this possible w/o subqueries.  I'm pursuing an SQLIte solution so I
can include it in a Ruby on Rails library I'm writing.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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_a set avalue = (
select bvalue from table_b where akey=bkey
union all
select avalue);

-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 `table_a` (`key`, `value`, `str`) VALUES (1,   1.0, 'mon');
INSERT INTO `table_a` (`key`, `value`, `str`) VALUES (2,   2.0, 'tue');
INSERT INTO `table_a` (`key`, `value`, `str`) VALUES (3,   3.0, 'wed');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (1, 101.0, 'mon');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (2, 102.0, 'may');
INSERT INTO `table_b` (`key`, `value`, `str`) VALUES (4, 104.0, 'jun');

SELECT 'BEFORE UPDATE'; SELECT * FROM `table_a` ORDER BY `key`;

UPDATE table_a
   SET value = (SELECT table_b.value FROM table_b WHERE table_a.key=table_b.key)
 WHERE EXISTS (SELECT 1 FROM table_b WHERE table_a.key=table_b.key);

SELECT 'AFTER UPDATE'; SELECT * FROM `table_a` ORDER BY `key`;
=
results in
=
sqlite> .read sqlite_update_test.sql
BEFORE UPDATE
1|1.0|mon
2|2.0|tue
3|3.0|wed
AFTER UPDATE
1|101.0|mon
2|102.0|tue
3|3.0|wed
=
The WHERE EXISTS condition is required to prevent updating rows where
there is NOT a match.  Pavel's suggestion (which was identical except
for the WHERE EXISTS clause) would set table_a.value fields to NULL on
rows where there wasn't a match.

So I see why David says it's a "little redundant": both sub-queries
contain 'FROM table_b WHERE table_a.key=table_b.key'.  But it works.
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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);
> INSERT INTO "table_b" ("key", "value") VALUES (1, 101.0), (2, 102.0),(4, 
> 104.0);
>
> In an imaginary version of SQLite ;) this might be written as:
>
> # UPDATE table_a
> #   JOIN table_b
> #    SET table_a.value = table_b.value
> #  WHERE table_a.key1 = table_b.key
>
> resulting in table_a:
>
> key | value
> 1   | 101
> 2   | 102
> 3   |   3
>
> ... that is to say, update table_a.value from table_b.value, but only
> on rows where table_a.key = table_b.key
>
> I've pored over the UPDATE syntax, but I don't see a way to do this.
> What's the idiom in SQLite?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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);
INSERT INTO table_b (bkey, bvalue) VALUES (1, 101.0);
INSERT INTO table_b (bkey, bvalue) VALUES (2, 102.0);
INSERT INTO table_b (bkey, bvalue) VALUES (4, 104.0);

UPDATE table_a SET avalue=(SELECT bvalue FROM table_b WHERE akey=bkey)
WHERE EXISTS(SELECT 1 FROM table_b WHERE akey=bkey);

I think there is no multi-table update idiom in SQLite because there
isn't one in SQL.

--David 

Robert Poor writes:
> 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);
> INSERT INTO "table_b" ("key", "value") VALUES (1, 101.0), (2, 102.0),(4, 
> 104.0);
> 
> In an imaginary version of SQLite ;) this might be written as:
> 
> # UPDATE table_a
> #   JOIN table_b
> #SET table_a.value = table_b.value
> #  WHERE table_a.key1 = table_b.key
> 
> resulting in table_a:
> 
> key | value
> 1   | 101
> 2   | 102
> 3   |   3
> 
> ... that is to say, update table_a.value from table_b.value, but only
> on rows where table_a.key = table_b.key
> 
> I've pored over the UPDATE syntax, but I don't see a way to do this.
> What's the idiom in SQLite?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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);
INSERT INTO "table_b" ("key", "value") VALUES (1, 101.0), (2, 102.0),(4, 104.0);

In an imaginary version of SQLite ;) this might be written as:

# UPDATE table_a
#   JOIN table_b
#SET table_a.value = table_b.value
#  WHERE table_a.key1 = table_b.key

resulting in table_a:

key | value
1   | 101
2   | 102
3   |   3

... that is to say, update table_a.value from table_b.value, but only
on rows where table_a.key = table_b.key

I've pored over the UPDATE syntax, but I don't see a way to do this.
What's the idiom in SQLite?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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.

My component wxSQLite3 (a SQLite wrapper for wxWidgets applications)
includes the sources for SQLite with support for the mentioned extension
functions without requiring to load the extension. All what's required
is to compile a single C source file (which in turn includes all other
necessary source files) with the symbol SQLITE_ENABLE_EXTFUNC defined.

The source code can be downloaded from here:

http://sourceforge.net/projects/wxcode/files/Components/wxSQLite3/

And here you find some additional information which might be helpful
for building SQLite:

http://wxforum.shadonet.com/viewtopic.php?t=27217

Regards,

Ulrich

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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, avg).  Is there a workaround or 
>> other fix available?  
> 
> 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.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users