helllth / experiments Goto Github PK

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.

🤘

.

https://belis-testapi.cismet.de/secres/eyJhbGciOiJSUzI1NiJ9.eyJqdGkiOiIzIiwic3ViIjoiV2VuZGxpbmdNIiwiZG9tYWluIjoiQkVMSVMyIn0.VTfsT_VS22ZW6TTWRsIx2P2TzqgaeksZG7wSmPG0l0JlTsIXDC6VzL1DUVO5U5kzNQq7X5qwImf0ai-VVAJ5UXdeRURBzJ50ENP7-ZpMLjNywfOCq-b77fGtkba8gqteAzetP71vEn4IlqIFiKT6mnHYxUVujQxBEvNkZC39GgiswZmU5Kq5YjuQCOfxFHKF7rdokawDpbfbquTk4LNUGZZszsIHG-Lk6gOLjKWJK4eMDb-rJ2GxVBz0l97lF7kOTz1x5CZXGy5BghzYQDW8ZcnnnYAr5wHN5YkIjw_SUGyU6GOkx7nW3UKxw18KyPMqkVnQ7-_hHO80s-ZGf-2URw/beliswebdav/DOC-1461583202010-629789389.jpg.thumbnail.jpg

asd

sdfsdffsd

Mantis Info:

In der Statuszeile von WuNDa/DK sind die Werte für Breite und Länge vertauscht

Das Rathaus liegt bei 51° Nördlicher Breite und 7° Ostlicher Länge

Angezeigt wird "7.20,51.27"

Die Beschreibung im Auswahlmenü der Bezugssystem ist korrekt (WGS84-Breite/Laenge)

Additional Information:

Platform: PC

OS: Windows XP/Vista

OS Version: 1

Product Version:

Target Version:

Reproducibility: immer

Priority: normal

Severity: Unschönheit

ID: 0002602

Entscheidungen

• Erste Entscheidung
• Zweite Entscheidung
• Dritte Entscheidung