SV: SQL delete and update at the same time

Hi Michael,
Thanks for taking interest in my problem.
My biggest problem is that I’m not so familiar with SQL, and having problems to see how I can realize your suggestion.
I’ve been testing in pgAdmin with 2 tables:
linesegments and buffered.

linesegments (splitted line strings into segments)
fid (integer, the same value for all line segments from the original line)
the_geom (Postgis geometry LineString)
edited, integer set to 0 (afterwards, 1 = shall be deleted, 2 = not to be deleted)
gid (integer, PRIMARY key)

buffered (polygons from bluffering original lines, thin buffer
fid (integer, the same value as the original line strings)
the_geom (Postgis geometry Polygon)
gid (integer, PRIMARY key)

This is what I’ve tried in pgAdmin4 from reading your comment
UPDATE linesegments l
set edited = CASE
WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited = null) AND
NOT b.fid=l.fid) THEN
WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited = null) AND
b.fid=l.fid) THEN 2
END

But the line segments that are within several buffer-polygon is set to 1 and the line segments that are within only one buffer polygon is set to 2. I thought this SQL-command would at least have on segment line set to 2 where there are duplicates. And it is very slow, although I created gist index on the two tables.
Can you see what I’m doing wrong?

Kind regards,
Paul
Från: Michael Lewis [mailto:mlewis(at)entrata(dot)com]
Skickat: den 24 juni 2020 21:33
Till: Malm, Paul (Operations AIM)
Kopia: PostgreSQL General
Ämne: Re: SQL delete and update at the same time

But how can I set the edited vale = 1 on the objects (line segments) that are not deleted (in the current buffer) at the same time so it won’t be deleted in the next run with an adjacent buffer?

You might want to create a temporary table to hold unique identifiers of all records that you want to either delete or edit, and make use of a RETURNING clause to determine which got deleted, then update the records NOT IN that returning clause values (or use EXCEPT). Perhaps that would get you where you want to end up.