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

Reply via email to