I have the following query which joins two tables and returns two geom columns, I would now like to make a line between the two geom columns and set that as the Geom for the Drops table.
Table 1 - Drops (contains the required geoms, and the node names)
Table 2 - Nodes (contains the two node names I would like to draw a line between)
select drops."DP Name", drops."East Node Name", node_dp."Geom" as dp_geom, east_node."Geom" as eastnode_geom from crm."Drops" drops join crm."Nodes" node_dp on drops."DP Name" = node_dp."Node Name" join crm."Nodes" east_node on drops."East Node Name" = east_node."Node Name"