Update column of a large set of records with a calculated value I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per actual result involved. 

update prediction
set points = (
    @points = 0
    if(prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB)
        @points = @points + 3
    if (prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB)
        @points = @points + 1
    select @points
)   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)


what I am wondering about is:


Is there a way of doing the calculation which in reality is more complex inline? Right now I can only get it to work if I put it in a function but from what I understand this means it's constantly re-calculted even if the input is the same (which I quite likely in allot of the predictions)
Would this way lock the entire range of records? And if so is there a way to prevent this? For example by copying it all to a table variable and then doing the calculation on that and then updating the records once all calculations are done?


am I on the right track here or would you write it in a different way?
Try this:

update prediction
set points = 
    (
        0 
        + 
        CASE WHEN prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB
        THEN 3 ELSE 0 END
        +
        CASE WHEN prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB
        THEN 1 ELSE 0 END
    )   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)


AFAIK - if function is not depend on data in query, then it calls one time per query,

AND

if the function is deterministic and schema bound - it will not call multiple times for the same data, BUT there has to be an index on argument data AND optimizer must want to use that index

If you want per-row locking, use ROWLOCK hint