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:
我有一个邮政编码表,我想用3个最近的邻居更新每个邮政编码。即填写此表中的空白:
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:
我已经找到了一个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';
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:
但是,这将导致为每个行更新运行3个选择查询。如果有一些方法来执行这个伪代码所表达的内容,那将会更有效:
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.
无论如何,从select返回的行被放入一个可以用来更新多个字段的表单中吗?谢谢。
4 个解决方案
#1
11
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'
#2
1
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 * on how to transform columns to rows:
我在*上找到了关于如何将列转换为行的相关问题:
MySQL - 行到列
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]:
这是一个模拟MySQL [1]中的ROW_NUMBER()功能的hack:
SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
#3
0
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.
看到它真正的SQL就更容易了。
Note the first column is specified as a constant in quotes. For this to work postcode
must be a UNIQUE
or PRIMARY
index.
请注意,第一列被指定为引号中的常量。为此,邮政编码必须是UNIQUE或PRIMARY索引。
#4
-1
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?
通常,存储可以从已经存储的数据计算的数据是坏想法(TM)。如果您的应用程序突然需要4个最接近的邮政编码,会发生什么?如果邮政编码边界发生变化怎么办?
Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.
假设距离计算不是很复杂,从长远来看,你最好不要明确地存储这些数据。
#1
11
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'
#2
1
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 * on how to transform columns to rows:
我在*上找到了关于如何将列转换为行的相关问题:
MySQL - 行到列
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]:
这是一个模拟MySQL [1]中的ROW_NUMBER()功能的hack:
SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
#3
0
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.
看到它真正的SQL就更容易了。
Note the first column is specified as a constant in quotes. For this to work postcode
must be a UNIQUE
or PRIMARY
index.
请注意,第一列被指定为引号中的常量。为此,邮政编码必须是UNIQUE或PRIMARY索引。
#4
-1
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?
通常,存储可以从已经存储的数据计算的数据是坏想法(TM)。如果您的应用程序突然需要4个最接近的邮政编码,会发生什么?如果邮政编码边界发生变化怎么办?
Assuming the distance calculation isn't very complex, you'll be better off in the long run not explicitly storing this data.
假设距离计算不是很复杂,从长远来看,你最好不要明确地存储这些数据。