@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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
