MySQL UPDATE 与同一个表的 SUBQUERY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10359534/
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 with SUBQUERY of same table
提问by bjornkock
I am working with a complex MySQL database table that collects form data. I have simplified the layout in an example table called testbelow:
我正在使用一个复杂的 MySQL 数据库表来收集表单数据。我在下面名为test的示例表中简化了布局：
|FormID|FieldName| FieldValue |
| 1 | city | Houston |
| 1 | country | USA |
| 2 | city | New York |
| 2 | country |United States|
| 3 | property| Bellagio |
| 3 | price | 120 |
| 4 | city | New York |
| 4 |zip code | 12345 |
| 5 | city | Houston |
| 5 | country | US |
Through phpMyAdmin I need to make global updates to some tables, specifically I want to updateall FieldValueentries to "United States of America" with the FieldName"country" that have the same FormIDas the FieldName"city" and the FieldValue"Houston".
通过phpmyadmin我需要让全球更新一些表格，具体我要更新所有fieldValue方法条目“美利坚合众国”与字段名“国家”具有相同的FormID的字段名“城市”和fieldValue方法“休斯敦” .
I can easily displaythese entries with a SELECT statement by either using a SUBQUERY or by using an INNER JOIN:
通过使用 SUBQUERY 或使用 INNER JOIN，我可以使用 SELECT 语句轻松显示这些条目：
SELECT FieldValue
FROM test
WHERE FormID
IN (
SELECT FormID
FROM test
WHERE FieldName = "city"
AND FieldValue = "Houston"
)
AND FieldName = "country"
Or:
或者：
SELECT a.FieldValue
FROM test a
INNER JOIN test b ON a.FormID = b.FormID
WHERE a.FieldName = "country"
AND b.FieldName = "city"
AND b.FieldValue = "Houston"
However I try to compose my UPDATEstatement I get some form of MySQL-error indicating that I cannot reference the same table in either a subqueryor inner joinor unionscenario. I have even created a viewand tried to reference this in the update statement, but no resolve. Does anyone have any idea how to help me?
但是，我尝试编写我的UPDATE语句，我得到某种形式的 MySQL 错误，表明我无法在子查询或内部联接或联合方案中引用同一个表。我什至创建了一个视图并尝试在更新语句中引用它，但没有解决。有谁知道如何帮助我？
回答by Reign.85
You have to use a temporary table, because you can't update something you use to select. A simple exemple:
您必须使用临时表，因为您无法更新用于选择的内容。一个简单的例子：
This will not working :
这将不起作用：
UPDATE mytable p1 SET p1.type= 'OFFER' WHERE p1.parent IN
(SELECT p2.id from mytable p2 WHERE p2.actu_id IS NOT NULL);
This will do the job:
这将完成这项工作：
UPDATE mytable p1 SET p1.type= 'OFFER' WHERE p1.parent IN
(SELECT p2.id from (SELECT * FROM mytable) p2 WHERE p2.actu_id IS NOT NULL);
"from (SELECT * FROM mytable) p2" will create a temporary duplicate of your table, wich will not be affected by your updates
"from (SELECT * FROM mytable) p2" 将创建您的表的临时副本，这不会受到您的更新的影响
回答by GDP
Aliasing should do the trick, if I'm understanding correctly:
如果我理解正确，别名应该可以解决问题：
UPDATE test AS a
JOIN test AS b ON a.id = b.id
SET a.name = 'New Name'
WHERE a.id = 104;
Is this not working for you? UPDATE: This was tested and works on MySQL v5.6.
这不适合你吗？ 更新：这已经过测试并且适用于 MySQL v5.6。
