Q:

Postgresql: update multiple rows in one table, given an array of IDs from another table

I have two tables: one containing distinct persons and another table containing place names. Every person is coupled to a place name ID - and the place name ID gives more information about the place (for example the name, longitude and latitude). 
The place name table is skewed, there are a lot of semi-duplicates (names written a bit differently e.g. London/Londen). For every place name I now also have the 'real' place name via Google API. 
Persons:
ID    Name     Birthplace
1     John     1
2     Sarah    2
3     Jane     3
4     Tom      4

Place names:
ID   PlaceName       GooglePlaceName
1    New York City   New York, NY, USA    
2    Amsterdam       Amsterdam, Netherlands
3    Londen          London, UK
4    London          London, UK

So when looking at this data, Jane and Tom are actually from the same place. 
I already have a query which gets the duplicate IDs from the place name table: 
SELECT id FROM placenames WHERE googleplacename IN (SELECT googleplacename FROM placenames GROUP BY googleplacename HAVING COUNT (googleplacename) > 1);

This returns
    ID
1   3
2   4

Now I'm wondering if it's possible to update the person table, so Jane and Tom both get the same Birthplace ID (doesn't matter if it's 3 or 4) and afterwards remove the duplicate rows from the place name table so either the place name with ID 3 or the place name with ID 4 remains, depending on which one has stayed in the persons table. 
If I'm totally going in the wrong direction, by trying to solve this with SQL I'd also like to know. I'm using Java and Spring to access the database.

A:

Since, it doesn't matter which id is used to replace, lets take the first id in a list of duplicates.
i.e. 
birthplace
3
4

becomes
birthplace
3
3

to do this first create a table mapping original & replacement id values
your select statement has the original ids, to that you can add the replacement ids using the window function first_value partitioned by googleplacename
Use this mapping table in the from clause of the update persons statement, joining on records where birthplace equals an original_id but not a replacement_id
UPDATE persons
SET birthplace = replacement_id

FROM (
  SELECT id original_id, FIRST_VALUE(id) OVER (PARTITION BY googleplacename) replacement_id
  FROM placenames 
  WHERE googleplacename IN (
    SELECT googleplacename FROM placenames GROUP BY 1 HAVING COUNT(*) > 1
  ) 
) replacement_table
WHERE birthplace = original_id
  AND birthplace != replacement_id

