SQL UPDATE SET one column to be equal to 0.000 when there are two identical values

Question

So here is the problem i'm trying to update column distance, first to sum the distance for each ownerID and after when there is a record with the same id address the first record should memories thee distance and the second should be 0.000

This is the expected result

So far I did the first part that calculates the distance using this code

UPDATE Action_Distance
SET [distance]=(SELECT sum([distance])
               FROM Action a2
               WHERE [name]='travel' and a2.ownerID = Action_Distance.ownerId 
               )

               WHERE [name]='drive_through' 

I don't understand how it should be done.

Answer 1

Assuming that id_action is unique for each row, then CASE expression together with a NOT EXIST subquery can be used in this way:

UPDATE Action_Distance A
SET [distance] = CASE
                    WHEN NOT EXISTS (SELECT 'anything' 
                                     FROM Action_Distance B
                                     WHERE B.[name] = 'drive_through' 
                                       AND a.ownerId = b.ownerId
                                       AND a.id_action > b.id_action)
                       THEN (SELECT SUM([distance])
                             FROM Action a2
                             WHERE [name] = 'travel' 
                               AND a2.ownerID = a.ownerId)
                       ELSE 0
                 END
WHERE 
    [name] = 'drive_through'