How to update each row that shares the same id differently in postgresql?

Hope this isn’t too hard to explain. I need to update the first table with addresses from the second

So basically I have mytable1 that has the following columns:

id (pkey) | super_id | address          | new_ref
101       | 1000     | 'wrong address'  | empty
102       | 1000     | 'wrong address2' | empty
103       | 1000     | 'wrong address3' | empty

And then I have mytable2 that actually has the correct addresses:

super_id | address          | new_ref (pkey)
1000     | 'right address'  | 1
1000     | 'right address2' | 2
1000     | 'right address3' | 3

I need to fill mytable1 with the correct addresses in mytable2 and they have the super_id in common

However when I try to do

update mytable1 a set address = (select address from mytable2 b where a.super_id = b.super_id)

It returns an error because obviously super_id is duplicated

Is there a way to update all address rows into mytable1? An insert could probably work but there’s already a lot of data on mytable1

Answer

This is tricky. If I understand correctly, you need to enumerate each of the tables for the update:

update mytable1 mt
    set address = mt2.address
    from (select mt.*, row_number() over (partition by super_id order by id) as seqnum
          from mytable1 mt
         ) mt1 join
         (select mt2.*, row_number() over (partition by super_id order by new_ref) as seqnum
          from mytable2 mt2
         ) mt2
         on mt2.super_id = mt1.super_id and mt2.seqnum = mt1.seqnum
     where mt1.id = mt.id