Update multiple columns using select joins on 2 tables

[williey]

I need a more efficient way to update 5 columns in table 1 using 5 columns from table 2. But the select criteria requires a 2 tables join between 1 and 2.

Eg.

update A a
set (a.col1, a.col2, a.col3, a.col4, a.col5) =
(select b.col1, b.col2, b.col3, b.col4, b.col5
from B b
where a.col10 = b.col10
and a.col11 = b.col11
and a.col12 = b.col12)

The above method takes too long to update 5 columns with about 40k rows.

 

[carp]

If you haven't done so already, you might want to consider a composite index on col10, col11, and col12 of both tables.

 

[williey]

Both tables have indexes.

 

[sem]

Make sure that the index on table B is used. Write a hint or just compute statistics.

Regards, Dima

http://seminihin.narod.ru