mySQL - 使用返回多行的选择更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5985264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL - update multiple columns with a select returning multiple rows
提问by spiderplant0
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 返回的行是否可以放入可用于更新多个字段的表单中？谢谢。
回答by Thomas
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'
回答by rkg
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:
我在 Stackoverflow 上找到了有关如何将列转换为行的相关问题：
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() 功能的一个技巧：
SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
回答by James C
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 postcodemust be a UNIQUEor PRIMARYindex.
请注意，第一列在引号中指定为常量。为此，postcode必须是一个UNIQUE或PRIMARY索引。
回答by Chris Morgan
Anytime I see a table with columns that have 1-up counters after their names, I get concerned.
每当我看到一个表的列的名称后面都有 1-up 计数器时，我就会感到担忧。
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 4closest 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.
假设距离计算不是很复杂，从长远来看，不显式存储这些数据会更好。
