SQL: Update every entry with value from another entry that share same column value

I have the following table trn_ReceiptLog

I am wondering if it’s possible to update amount of entry #1 to have same as entry #2 IF amount of entry #1 is 0?

I have over 5000 of these entries that need to be updated, basically something like:

UPDATE trn_ReceiptLog SET amount = (SELECT amount FROM trn_ReceiptLog WHERE receipt_type = 0) WHERE amount = 0

But I am not sure how to do it for all entries individually, do I need some sort of loop?

Condition 1: Receipt type will always be 0 of the one where amount needs to be taken from.

Condition 2: person_id will always be identical across two of these.

Condition 3 (Optional): Only perform this update IF there is only one receipt_type = 9 (Sometimes there might be 3 or 4 entries with same person_id and being receipt_type 9

Answer

You can use window functions to calculate the information needed for the conditions. Then the logic is simple:

with toupdate as (
      select t.*,
             max(case when receipt_type = 9 then amount else 0 end) over (partition by person_id) as amount_9,
             sum(case when receipt_type = 9 then 1 else 0 end) over (partition by person_id) as num_9s
      from t
     )
update toupdate
    set amount = amount_9
    where receipt_type = 0;