This is the modified update statement based on your feedback...

UPDATE a

SET a.field1 = CASE WHEN b.field1 IS NOT NULL

THEN b.field1

ELSE a.field1

END

FROM table1 a

INNER JOIN table2 b ON a.id = b.id AND a.status = -1

where a.datekey > '20100101'

-- table is in primary filegroup and datekey clustered index under primary filegroup. After adding the where condition the clustered index scan on table1 become clustered index seek on table1 also reducing the cost from 98% to 3%. But the cost is now split to other components like hash join (34%).. earlier hash join is 1%.

But table2 clustered index seek is good since it is going to join with that big table.

Provide me if you can make some more suggestions.

volume of the tables:

table1 - 250million

table2 - 4000 records