我有一个邮政编码表,我想用它的 3 个最近邻居更新每个邮政编码。即填写此表中的空白:
postcode nearestPostcode1 nearestPostcode2 nearestPostcode3
_______________________________________________________________
KY6 1DA - - -
KY6 1DG - - -
KY6 2DT - - -
KY6 1RG - - -
....
我已经想出了一个 SELECT 查询来查找最近的邮政编码,这是更新第一行的一种笨拙方法:
update table1 set
nearestPostcode1 = (select query for returning the first nearest postcode),
nearestPostcode2 = (select query for returning the second nearest postcode),
nearestPostcode3 = (select query for returning the third nearest postcode)
where postcode = 'KY6 1DA';
然而,这将导致为每个行更新运行 3 个选择查询。如果有某种方法可以执行此伪代码所表达的操作,那么效率会更高:
update table1 set
(nearestPostcode1, nearestPostcode2, nearestPostcode3) =
(select query to return the 3 nearest postcodes)
where postcode = 'KY6 1DA';
上面的“选择查询”如下所示:
select postcode from postcodeTable
order by <equation to calculate distance> ASC
limit 3
无论如何,从选择返回的行是否可以放入可用于更新多个字段的表单中?
谢谢。