Thank You. On 26 October 2010 13:14, Igor Tandetnik <itandet...@mvps.org> wrote: > Paul Sanderson <sandersonforens...@gmail.com> wrote: >> I have two tables, table b is a subset of table a. both tables have >> the same primary key >> >> I want to update the rows from table a with a single column from table >> b, what sql command would be most efficient for this? > > update a set ColumnToUpdate = coalesce( > (select ColumnToUpdate from b where b.KeyColumn = a.KeyColumn), > ColumnToUpdate); > > -- or > > insert or replace into a(ColumnToUpdate, AllOtherColumns) > select b1.ColumnToUpdate, a1.AllOtherColumns > from b1 join a1 on b1.KeyColumn = a1.KeyColumn; > > The second statement could be faster if b is much smaller than a, but is more > verbose and has to be updated whenever schema changes. Time both on real > data, see which one works better for you. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
-- Paul Sanderson Sanderson Forensics +44 (0)1325 572786 www.sandersonforensics.com http://www.twitter.com/sandersonforens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
