Optimising update which involves same table in target and where

[boardtc]

I have a very large with milions of rows:

I will attempts to create a reduced SSCCE to explain my problem. The below applies for multiple locations, and I will attempt to describe my problem assuming one location. The metric are also foreign keys so to get the real value I have to do a further join

tableA
date   metric    measure  value
1sep   metric1   1        10 
1sep   metric1   2        8 
1sep   metric1   3        13 
...
1sep   metric1   20       7 
1sep   metric2   1        15
1sep   metric2   2        17
1sep   metric2   3        2
...
1sep   metric2   20       13

After a bunch of work I am left with this result set for metric1

date   metric    measure  value value1
1sep   metric1   3        13

I then need to fill value1 with the value at the same date for metric2 at measure 3 [2], so I need to do an update to tableA. Because you can't specify the target table (for update) in the from clause I do a join to tableA on date, metric and measure and match everything up in the where clause. I believe all my indices are correct. This takes in the orders of minutes to execute.

Hope it's clear enough. Is there a quicker way? Maybe without temporary tables if possible.

Thanks, Tom.