How to update rows with the value from other rows that have the same foreign key

Question

Thanks in advance! This is what I have:

primary_key | foreign_key   | field_to_update
---------------------------------------------
          1 |             1 |              A 
          2 |             1 |              - 
          3 |             2 |              B
          4 |             2 |              -
---------------------------------------------

Expected results:

primary_key | foreign_key   | field_to_update
---------------------------------------------
          1 |             1 |              A 
          2 |             1 |              A 
          3 |             2 |              B
          4 |             2 |              B
---------------------------------------------

How would I proceed? I don't think I can do:

UPDATE table
SET field_to_update = field_to_update
WHERE ...

That doesn't seem to make sense. Could someone help me with this?

Answer 1

You can use update with join:

update t join
       (select foreign_key, max(field_to_update) as field_to_update
        from t 
        group by foreign_key
       ) tt
       on t.foreign_key = tt.foreign_key
    set t.field_to_update = tt.field_to_update
    where t.field_to_update is null;