<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">I have a table containing tornado index values by zip code. A small number of the zip codes do not have values. I would like to update the tornado_index values for those records by simply using the value for the nearest zip_code (by comparing distance to the centroid) in the same table. The UPDATE statement that I use executes, but it appears to return the same record from the sub-select each time. Doesn't the sub-select get executed for each record, as it depends on a value (the_geom) from the outer table? Is there a better way to do this?<div><br></div><div><span class="Apple-style-span" style="font-family: 'Courier New'; ">-- Using postgres 8.3.6 and postgis 1.3</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New'; "><br></span></div><div><font class="Apple-style-span" face="'Courier New'">-- working.zip_tornado_index --</font></div><div><font class="Apple-style-span" face="'Courier New'">id int</font></div><div><font class="Apple-style-span" face="'Courier New'">zip_code text</font></div><div><font class="Apple-style-span" face="'Courier New'">tornado_index int</font></div><div><font class="Apple-style-span" face="'Courier New'">the_geom geometry (SRID=4326)</font></div><div><font class="Apple-style-span" face="'Courier New'"><br></font></div><div><font class="Apple-style-span" face="'Courier New'"><br></font></div><div><font class="Apple-style-span" face="'Courier New'">UPDATE working.zip_tornado_index</font></div><div><font class="Apple-style-span" face="'Courier New'">SET tornado_index = (</font></div><div><font class="Apple-style-span" face="'Courier New'"> SELECT b.tornado_index</font></div><div><font class="Apple-style-span" face="'Courier New'"> FROM working.zip_tornado_index b</font></div><div><font class="Apple-style-span" face="'Courier New'"> WHERE b.tornado_index IS NOT NULL</font></div><div><font class="Apple-style-span" face="'Courier New'"> AND ST_DWithin(the_geom,b.the_geom,0.1)</font></div><div><font class="Apple-style-span" face="'Courier New'"> ORDER BY ST_Distance(the_geom,b.the_geom) ASC NULLS LAST</font></div><div><font class="Apple-style-span" face="'Courier New'"> LIMIT 1</font></div><div><font class="Apple-style-span" face="'Courier New'">)</font></div><div><span class="Apple-style-span" style="font-family: 'Courier New'; ">WHERE tornado_index IS NULL</span></div><div><span class="Apple-style-span" style="font-family: 'Courier New'; ">;</span></div><div><font class="Apple-style-span" face="'Courier New'"><br></font></div><div><span class="Apple-style-span" style="font-family: 'Courier New'; "><br></span></div><div>Thanks,</div><div><span class="Apple-style-span" style="font-family: 'Courier New'; "><span class="Apple-style-span" style="font-family: Helvetica; ">Pete</span></span></div></body></html>