When I perform an update – rows keep updating if I run the query again

I am updating a table as follows:

UPDATE dw.distance_test ead
SET distance = tp.distance
FROM dw.distance_tp tp
WHERE ead.ref = tp.ref
AND  COALESCE(ead.distance,0) <> COALESCE(tp.distance,0)

Which gives the result ‘Query returned successfully: 6 rows affected, 26 msec execution time.’

If I run the same query again it still says gives the result ‘Query returned successfully: 6 rows affected, 26 msec execution time.’ – I would have thought it would say 0 rows affected as I’ve already updated the table. Could someone possibly explain what’s happening and what can be done to rectify it?

Thanks

Not sure if more info is required, please let me know

Answer

If the source contains ref doubles, for example

     distance_tp 
  ref distance
  1   100
  1   200

the condition COALESCE(ead.distance,0) <> COALESCE(tp.distance,0) is always true for one of the source rows watherver the target values is. So the target is always updated.