Update a field from a PostgreSQL point table (wherever it's NULL) with certain field value from closest polygon?

Question

Given a point table (table1) and a polygon table (table2) in PostgreSQL, I am trying to update a field from table1 wherever its value is NULL. This field should be updated with a certain field value (let's call it 'required_field') from the closest polygon in table2.

Both tables contain spatial index (gist) and are on projected coordinates. There are about 1.8 million points and 11000 polygons. 97.5% of points have got field_1 = NULL.

To obtain the value of required_field from closest polygon:

SELECT t2.required_field
FROM table1 t1
CROSS JOIN LATERAL
    (SELECT required_field, geom
     FROM table2
     ORDER BY t1.geom <-> geom
     LIMIT 1) AS t2

To update field_1 from table1 wherever its value is NULL:

UPDATE table1
SET field_1 = ...
WHERE field_1 IS NULL

However, I don't know how I could combine these two queries to produce the result I need.

I am trying with the following query, but it seems to run forever (more than 4 hours and it keeps going)

UPDATE table1
SET field_1 = required_field
FROM table1 AS t1
CROSS JOIN LATERAL
    (SELECT required_field, geom
     FROM table2
     ORDER BY t1.geom <-> geom
     LIMIT 1) AS t2
WHERE field_1 IS NULL;

Is this query actually right? Any other way to do what I am after?

.......

explain throws the following:

"Update on midpoints_wal_scot  (cost=0.15..1318769591009.67 rows=15380552946848 width=1543)"
"  ->  Nested Loop  (cost=0.15..1318769591009.67 rows=15380552946848 width=1543)"
"        ->  Nested Loop  (cost=0.15..3429851.15 rows=3868432 width=2940)"
"              ->  Seq Scan on midpoints_wal_scot t1  (cost=0.00..81053.14 rows=3868432 width=38)"
"                    Filter: (sop_id IS NULL)"
"              ->  Subquery Scan on t2  (cost=0.15..0.86 rows=1 width=2902)"
"                    ->  Limit  (cost=0.15..0.85 rows=1 width=1447)"
"                          ->  Index Scan using sop_polyg_geom_idx on sop_polyg  (cost=0.15..7672.73 rows=11029 width=1447)"
"                                Order By: (geom <-> t1.geom)"
"        ->  Materialize  (cost=0.00..143643.71 rows=3975914 width=62)"
"              ->  Seq Scan on midpoints_wal_scot  (cost=0.00..81053.14 rows=3975914 width=62)"

Answer 1

The update query is taking forever because you have introduce a cross join between the point table and a copy of it (UPDATE table1 SET ... FROM table1 AS t1). We can see that in the explain plan with the number of rows to be updated (rows=15 380 552 946 848)

When doing an update, each row is processed individually so you don' t need the lateral join. You could directly do

UPDATE table1
SET field_1= (SELECT required_field
     FROM table2 t2
     ORDER BY table1.geom <-> t2.geom
     LIMIT 1)
WHERE field_1 IS NULL;

To update multiple fields, you must enclose them in parentheses:

UPDATE table1
SET (field_1, field_2) = (SELECT required_field, another_field
     FROM table2 t2
     ...