Conditional update

From: Lahari Sengupta <jhinik8(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Conditional update
Date: 2018-02-12 12:05:04
Message-ID: CAKEx41rZ3LNW_d4ToM2x7gvnKVrvCOLZ2F+-K6Q9xoNzJ1znLw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have two table name servers and sites. Servers contains timing and
workmode and geometry values. Where sites contains geometry values and an
empty time_stamp column. I want to fill this time_stamp column. For this,
I want to check for all servers data whether it is within 9 unit distant
from each sites. Hence, servers data 1 will search for all the sites and if
it finds a site within 9 unit then it checks for its own work mode, if it
is true then it writes the time in time_stamp column of that sites record.
All the servers data with true work mode should find out one site. But
there can be more sites. So, all the sites records might not update. For
this I have written the following query. But not sure whether it is serving
my purpose properly or not. Can anyone suggest?

UPDATE sites st
SET time_stamp = timing FROM servers sv
WHERE st_distance(st.geom, sv.geom) < 9 AND st.workmode = TRUE
;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stuart 2018-02-12 14:33:04 Re: Conditional update
Previous Message Tom Lane 2018-02-09 14:32:16 Re: functions allowed in CHECK constraints