Robert Poor <rdp...@gmail.com> wrote: > 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 possible, set up your table so that INSERT OR REPLACE works for it. E.g. declare "key" field unique, or primary key. > 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)); > ================== > 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' Assuming "key" is declared unique: insert or replace into table_a(key, value, str) select key, value, str from table_b; If it's possible to have records in table_b without a match in table_a, and assuming you don't actually want to insert such records, then insert or replace into table_a(key, value, str) select b.key, b.value, b.str from table_b b join table_a a on (a.key = b.key); > (FWIW, both PostgreSQL and MySQL have extensions to UPDATE that make > this possible w/o subqueries. Yes, it is unfortunate that SQLite doesn't implement such an extension. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
