How to update with join for null values?

Question

I ma updating the my spatial data using update from like folowing.

update ways w
    set end_node_id = n.node_id
from nodes n
where st_endpoint(w.shape) = n.shape 
   and w.created_at >= current_date 
   and w.created_at < current_date + interval '1 day';

This query updates the end_node_id of way with node_id of nodes table. But if any node deleted or moded to another location, the end_node_id should set as null after the query run again.

How can I change this sql?

Answer 1

Possibly the simplest method is to set everything to NULL before doing the update:

update ways w
    set end_node_id = NULL;

Then your update will change the NULL to something else. This does incur the overhead of updating some (many?) rows twice. But it is simple.

An alternative method requires a primary key on ways; let me call that way_id:

update ways w
    set end_node_id = n.node_id
from ways w2 left join
     nodes n
     on st_endpoint(w2.shape) = n.shape
where w2.way_id = w.way_id;