Re: SQL delete and update at the same time - Mailing list pgsql-general

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;