On Fri, Apr 1, 2011 at 21:36, Igor Tandetnik <itandet...@mvps.org> 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
