SQL delete and update at the same time

From: <paul(dot)malm(at)lfv(dot)se>
To: <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: SQL delete and update at the same time
Date: 2020-06-24 18:42:55
Message-ID: 9F4A5D06-1815-4723-9189-99F4C5067C53@lfv.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list, the question is in the ens of the Mail.
I have a problem with adjacent polygons when converting them to lines,
as I would like to only have one line in the boundaries between the former polygons. Now it could be up to 5.
I’ve been thinking of:

1. Creating a thin buffer around the line strings with the featureId from the line strings as a new table.

2. Then I should break the line strings to line segments into a new Table where the line segments gets the featureId from the line string.

3. Create a column (edited) in the new line-segment Table with a default value of 0

4. Then delete from the line segments table all segments that are within the buffer, but not the line segments that have the same featureId as the current buffer and edited value must be 0 to be deleted.

5. The line segments that are not deleted in the current buffer shall be updated (edited = 1).

6. Do step 4 and 5 until the all objects in the buffered table are executed.
I’ve come this far:
DELETE FROM "LineSegments" USING buffered as h WHERE
ST_Contains(h.the_geom, " LineSegments".the_geom) AND
"LineSegments".edited = 0 AND
NOT h.fid=" LineSegments".fid;

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?
Kind regards,
Paul

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2020-06-24 19:10:14 Re: Persistent Connections
Previous Message Bee.Lists 2020-06-24 17:55:00 Re: Persistent Connections