mysql select 返回列_mySQL – 使用select返回多行来更新多个列

我有一个邮政编码表,我想用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 ASC

limit 3

无论如何,从select返回的行被放入一个可用于更新多个字段的表单中吗？

谢谢.