MySQL UPDATE与同一表的SUBQUERY [英] MySQL UPDATE with SUBQUERY of same table

问题描述

我正在使用一个复杂的MySQL数据库表来收集表单数据.我在下面名为 test 的示例表中简化了布局:

I am working with a complex MySQL database table that collects form data. I have simplified the layout in an example table called test below:

|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      |

通过phpMyAdmin，我需要对某些表进行全局更新，特别是我想使用 FieldName将所有 FieldValue 条目 update 到美利坚合众国" 国家/地区"，该国家/地区与 FieldName "city"和 FieldValue 休斯顿"具有相同的 FormID .

Through phpMyAdmin I need to make global updates to some tables, specifically I want to update all FieldValue entries to "United States of America" with the FieldName "country" that have the same FormID as the FieldName "city" and the FieldValue "Houston".

通过使用SUBQUERY或使用INNER JOIN，我可以使用SELECT语句轻松显示这些条目:

I can easily display these entries with a SELECT statement by either using a SUBQUERY or by using an INNER JOIN:

SELECT FieldValue
FROM test
WHERE FormID
IN (
   SELECT FormID
   FROM test
   WHERE FieldName =  "city"
   AND FieldValue =  "Houston"
   )
AND FieldName =  "country"

或者:

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"

但是，我尝试编写我的 UPDATE 语句时，出现某种形式的MySQL错误，指示我无法在子查询或内部引用同一表加入或联盟方案.我什至创建了一个视图，并试图在update语句中引用它，但没有解决方法. 有人知道如何帮助我吗?

However I try to compose my UPDATE statement I get some form of MySQL-error indicating that I cannot reference the same table in either a subquery or inner join or union scenario. I have even created a view and tried to reference this in the update statement, but no resolve. Does anyone have any idea how to help me?

推荐答案

您必须使用临时表，因为您无法更新用于选择的内容.一个简单的例子:

You have to use a temporary table, because you can't update something you use to select. A simple exemple:

这不起作用:

UPDATE mytable p1 SET p1.type= 'OFFER' WHERE p1.parent IN 
    (SELECT p2.id from mytable p2 WHERE p2.actu_id IS NOT NULL);

这将完成工作:

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"将创建您的表的临时副本，这将不受更新的影响

"from (SELECT * FROM mytable) p2" will create a temporary duplicate of your table, wich will not be affected by your updates

这篇关于MySQL UPDATE与同一表的SUBQUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！