Re: [sqlite] updating records in table A from joined records in table B
@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
> ... 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
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
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