Update table with duplicate rows in another table

Question

I have three tables one called facility, one hotel and the other hotel_facility which is the join table for hotels and facilities. So far so good. I have many duplicates in the facility table. I want to update the join table and then delete the duplicates in the facility table. Finding the duplicates and deleting them is not a problem:

select o.id
from facility o
where exists ( select 'x' from facility i where i.name = o.name);

and then I delete based on this query. But how about updating the join table how would I go about doing this, I am using the latest version of Postgres. Can I use something like Update table using newest value in another table

Thanks

Answer 1

I think you need to do the update before doing the delete. This query will set the facility id to the lowest facility id for that name:

update hotel_facility hf
    set facility_id = (select min(f.facility_id)
                       from facility f join
                            facility f2
                            on f.name = f2.name
                       where f.id = hf.facility_id);

Then you can delete all but the minimum:

delete from facility f
    where exists (select 1
                  from facility f2
                  where f2.name = f.name and f2.id > f.id
                 );