Re: SQL delete and update at the same time
От
Michael Lewis
Тема
Re: SQL delete and update at the same time
Дата
Msg-id
CAHOFxGo_aAW68QjUbc73mNs5GSogaoUgAYkTkj-2oKJAVo9BMA@mail.gmail.com
Ответ на
Список
Дерево обсуждения
SQL delete and update at the same time <paul.malm@lfv.se>
Re: SQL delete and update at the same time Michael Lewis <mlewis@entrata.com>
SV: SQL delete and update at the same time <paul.malm@lfv.se>
Re: SQL delete and update at the same time Michael Lewis <mlewis@entrata.com>
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;
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;
В списке pgsql-general по дате отправления
От: Tom Lane
Дата:
