mySQL - update multiple columns with a select returning multiple rows

Question

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.

Answer 1

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'

Answer 2

You can do something similar to this:

UPDATE table1
SET
nearestPostcode1 = pc1,
nearestPostcode2 = pc2,
nearestPostcode3 = pc3
FROM 
(SELECT pc1, pc2, pc3 FROM ....) t
WHERE 
postcode = 'KY6 1DA';

I found this related question on Stackoverflow on how to transform columns to rows:

MySQL - Rows to Columns

In your case, you can do something like

SELECT 
IF(@rownum=1,postcode,'') ) AS pc1, 
IF(@rownum=2,postcode,'') ) AS pc2, 
IF(@rownum=3,postcode,'') ) AS pc2, 
FROM
(SELECT postcode 
FROM postcodeTable 
ORDER BY <equation to calculate distance> ASC 
LIMIT 3)

Here is a hack to simulate the ROW_NUMBER() functionality in MySQL [1]:

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;

Answer 3

I think you could do this with the pseudo-code:

REPLACE INTO table1 (postcode, nearestPostcode1, nearestPostcode2, nearestPostcode3)
    SELECT "KY6 1DA", col1, col2, col3 FROM myTable WHERE ...;

it'd be easier to specify it seeing the real SQL.

Note the first column is specified as a constant in quotes. For this to work postcode must be a UNIQUE or PRIMARY index.

Answer 4

Anytime I see a table with columns that have 1-up counters after their names, I get concerned.

In general, it is a Bad Idea (TM) to store data that can be calculated from data that is already stored. What happens if your application all of a sudden needs the 4 closest postal codes? What if the postal code boundaries change?

Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.