3

I am writing a PostGIS query in which I have to update the geometry column in table A with the value of the geometry column in Table B.

Table B contains only 300 Rows while Table A contains more than 40000 rows.

The problem is that the query is updating complete table A (40000 rows) instead of only updating rows where the join is valid.

Here is my query

UPDATE osm_2023_roads
SET way=tab2.geom
FROM osm_2023_roads tab1 
INNER JOIN
roads2023insideborderchecking tab2
ON tab1.osm_id=tab2.osm_id
WHERE tab1.osm_id=tab2.osm_id;

1 Answer 1

5

UPDATE..FROM creates an implicit join. You don't need to use the target table as the source, then join to it. If you do, it will result in a self-join:

Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

Which means you accidentally used osm_2023_roads as an additional source for an update of its own self, without norrowing down which rows are supposed to update which, and that enables them to match everything.
Use only your actual source in the FROM list and define how to match its rows to your target's in the WHERE:

UPDATE osm_2023_roads AS tab1 
SET way=tab2.geom
FROM roads2023insideborderchecking tab2
WHERE tab1.osm_id=tab2.osm_id;

You can debug things like that by adding a returning clause to see which target rows matched which rows in the source. Demo at db<>fiddle:

create table osm_2023_roads (osm_id, way) as values
  (1,'linestring(0 0,0 1,1 1,1 2,2 2,2 3)'::geometry),
  (2,'linestring(3 0,3 4)'),
  (3,'linestring(5 0,5 -2)');
create table roads2023insideborderchecking (osm_id, geom) as values
  (2,'linestring(99 99,88 77)'::geometry);
UPDATE osm_2023_roads target
SET way=tab2.geom
FROM osm_2023_roads tab1 
INNER JOIN
roads2023insideborderchecking tab2
ON tab1.osm_id=tab2.osm_id
WHERE tab1.osm_id=tab2.osm_id
RETURNING target.osm_id as "target.osm_id"
         ,tab1.osm_id as "tab1.osm_id"
         ,tab2.osm_id as "tab2.osm_id"
         ,st_astext(tab1.way) as way
         ,st_astext(geom) as geom;

You can see the self-join matching everything freely - that's because there's no WHERE condition constraining how your updated osm_2023_roads target table is supposed to match rows in osm_2023_roads posing as a source in the FROM list:

target.osm_id tab1.osm_id tab2.osm_id way geom
1 2 2 LINESTRING(3 0,3 4) LINESTRING(99 99,88 77)
2 2 2 LINESTRING(3 0,3 4) LINESTRING(99 99,88 77)
3 2 2 LINESTRING(3 0,3 4) LINESTRING(99 99,88 77)

Avoiding the self-join, you're only matching to your source, the way you specified:

UPDATE osm_2023_roads AS tab1 
SET way=tab2.geom
FROM roads2023insideborderchecking tab2
WHERE tab1.osm_id=tab2.osm_id
RETURNING tab1.osm_id
         ,tab2.osm_id
         ,st_astext(tab1.way) as way
         ,st_astext(geom) as geom;
osm_id osm_id way geom
2 2 LINESTRING(99 99,88 77) LINESTRING(99 99,88 77)
1
  • 1
    Thank you so much for the quick help and detailed explanation. Your help has solved the problem. Commented Mar 26 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.