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