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