MySQL-使用选择返回多行来更新多列 [英] mySQL - update multiple columns with a select returning multiple rows

问题描述

我有一个邮政编码表，我想用3个最近的邻居更新每个邮政编码.即填写此表中的空白:

I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table:

postcode  nearestPostcode1  nearestPostcode2  nearestPostcode3
_______________________________________________________________

KY6 1DA      -                -                  -
KY6 1DG      -                -                  -
KY6 2DT      -                -                  -
KY6 1RG      -                -                  -
....

我想出了一个SELECT查询来查找最近的邮政编码，这是一种笨拙的方式来更新第一行:

I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated:

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个选择查询.如果有某种方法可以执行此伪代码所表示的操作，则会更有效:

However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code:

update table1 set 
(nearestPostcode1, nearestPostcode2, nearestPostcode3) = 
(select query to return the 3 nearest postcodes)
where postcode = 'KY6 1DA';

上面的选择查询"看起来像这样:

The 'select query' in the above looks like this:

select postcode from postcodeTable 
order by <equation to calculate distance> ASC 
limit 3

无论如何，从选择返回的行是否存在可以用来更新多个字段的形式? 谢谢.

Is there anyway for the rows returned from the select to be put into a form that they can be used to update multiple fields? Thanks.

推荐答案

Update Table1
    Cross Join  (
                Select Min( Case When Z1.Num = 1 Then Z1.postcode End ) As PostCode1
                    , Min( Case When Z1.Num = 2 Then Z1.postcode End ) As PostCode2
                    , Min( Case When Z1.Num = 3 Then Z1.postcode End ) As PostCode3
                From    (
                        Select postcode 
                            , @num := @num + 1 As Num
                        From postcodeTable 
                        Where postcode = 'KY6 IDA'
                        Order By <equation to calculate distance> ASC 
                        Limit 3
                        ) As Z1
                ) As Z
Set nearestPostCode1 = Z.PostCode1
    , nearestPostCode2 = Z.PostCode2
    , nearestPostCode3 = Z.PostCode3
Where Table1.postcode =  'KY6 IDA'

这篇关于MySQL-使用选择返回多行来更新多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！