From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | paul(dot)malm(at)lfv(dot)se |
Cc: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: SQL delete and update at the same time |
Date: | 2020-06-25 20:51:12 |
Message-ID: | CAHOFxGo_aAW68QjUbc73mNs5GSogaoUgAYkTkj-2oKJAVo9BMA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Sorry, I don't know much about postgis at all. I assume you meant to have
THEN 1 in your update statement as well.
I notice b.fid=l.fid and NOT b.fid=l.fid in the two clauses. How about
separate update statements?
UPDATE linesegments l
set edited = 1
WHERE 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);
UPDATE linesegments l
set edited = 2
WHERE 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);
Something like this (and similar for b.fid=l.fid) should be equivalent to
the above as best I can figure. It might be more performant, but I don't
know the internals well enough to say for sure.
UPDATE linesegments l
set edited = 1
FROM buffered as b
WHERE
l.gid = l.gid and ST_Contains(b.the_geom, li.the_geom) AND
(l.edited = 0 OR l.edited =
null) AND
NOT b.fid=l.fid;
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bhalodiya, Chirag | 2020-06-26 03:00:04 | Re: PostGreSQL TDE encryption patch |
Previous Message | Tom Lane | 2020-06-25 20:25:00 | Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP |