MySQL UPDATE 与同一个表的 SUBQUERY

2022-01-05 00:00:00 sql mysql phpmyadmin

我正在使用一个复杂的 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，我需要对某些表进行全局更新，特别是我想将所有 FieldValue 条目FieldValue 更新为United States of America"，并使用 FieldName 与 FieldNamecity"和 FieldValueHouston"具有相同 FormID 的country".

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 错误，表明我无法在 子查询 或 内部引用同一个表加入或联合方案.我什至创建了一个视图并尝试在更新语句中引用它，但没有解决.有没有人知道如何帮助我?

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

相关文章