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 - - -
....
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';
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With