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 timing column. For this, I want to check for all servers data whether it is within 9 unit distant for 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. The algorithm is like this:
FOR each server data FOR each sites IF current server data is within 9 m distant of current site AND workmode is true THEN sites.time_stamp <- server.timng break sites loop
For this I have written the following query. But not sure whether it is serving my purpose properly or not. Can anyone suggest? Should I need to add any loop here in my query?
UPDATE sites st SET time_stamp = timing FROM servers sv WHERE st_distance(st.geom, sv.geom) < 9 AND sv.workmode = TRUE ;