PostGIS - For each polygon in table A, find nearest point in table B and update table A with value

Question

I have a table called sites of polygons, and a table called postcodes of points. For each row in sites I need to find the nearest point in postcodes, then update the sites column postcode from column pc in postcodes. Can anyone please help me write this in PostGIS?

This gets all the correct values, I just don't know how to do an update that can read these straight into the postcodes column:

SELECT DISTINCT ON(g1.key_column)  g1.key_column As key_column, g2.pc As 
postcode
FROM sites As g1, postcodes As g2   
WHERE g1.key_column <> g2.key_column AND ST_DWithin(g1.geom, g2.geom, 
1000)   
ORDER BY g1.key_column, ST_Distance(g1.geom, g2.geom)

Answer 1

Try something like this:

UPDATE tableA tA1 SET pc = 
(SELECT near_point.pc
FROM tableA tA2, 
LATERAL (SELECT pc 
         FROM tableB 
         ORDER BY tableB.geom <-> tA2.geom 
         LIMIT 1) near_point
WHERE tA2.id = tA1.id)