Proof read

[helllth]

Hi negroscuro,

I think your update statement is problematic, since the from clause in an update statement doesn't produce the cartesian product you are used from a select statement. Therefore it is not deterministic, meaning that it is not guaranteed that the statement always returns the same result.

If we reduce the dimension of the problem (from 2d to 1d) it is more easier to understand:

CREATE TABLE trees (
    name text,
    fromT int,
    toT int
);

CREATE TABLE vegetation (
    name text,
    fromV int,
    toV int,
    updated text
) ;



insert into trees (name, fromT, toT) 
values
('T1',0,8),
('T2',9,13),
('T3',0,6),
('T4',16,17);

insert into vegetation (name, fromV, toV, updated) 
values
('V1',0,10,''),
('V2',5,8,''),
('V3',8,15,'');

First I write a select statement and print out every vegetation that should be updated and with which matching tree dataset.

select v.name || ' should be updated with ' || t.name from trees t,vegetation v 
where 
-- within
(fromV <= fromT AND toV>= toT) OR
-- overlaps
((toV >= fromT AND toT>= toV ) OR (fromT<=fromV AND toT <= toV)) 
order by 1;

This will give us something like this:

Now I am writing an update statement with the same WHERE clause and the same text result as in the SELECT statement, but I update the field updated of the vegetation table:

update vegetation 
set updated= updated || vegetation.name || ' should be updated with ' || trees.name || ';' -- add the update stirg to the existing value to proof the the update set is only executed once
from trees
where 
-- within
(fromV <= fromT AND toV>= toT) OR
-- overlaps
((toV >= fromT AND toT>= toV ) OR (fromT<=fromV AND toT <= toV));

I can then have a look on the result with:

select name,updated from vegetation;

... and see something like:

Well, this is obviously not the result we are looking for 😉

---

TL;DR

Overthink the above statement. Maybe you can write a select statement first, insert that in a third table and go on with that new table.

🤘