MySQL UPDATE с SUBQUERY той же таблицы

Я работаю со сложной базой данных 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      |

Через phpMyAdmin Мне нужно сделать глобальные обновления для некоторых таблиц, в частности, я хочу обновить все записи FieldValue в "Соединенные Штаты Америки" с помощью FieldName "страны" которые имеют тот же FormID, что и FieldName "город" и FieldValue "Хьюстон".

Я могу легко отобразить эти записи с помощью инструкции SELECT либо с помощью SUBQUERY, либо с помощью 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, указывающую, что я не могу ссылаться на одну и ту же таблицу в подзапросе или внутренней join или union. Я даже создал представление и попытался ссылаться на него в инструкции обновления, но не решил. Кто-нибудь знает, как мне помочь?

Ответ 1

Вы должны использовать временную таблицу, потому что вы не можете обновлять то, что вы используете для выбора. Простой пример:

Это не сработает:

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

"из (SELECT * FROM mytable) p2" создаст временный дубликат вашей таблицы, на который не повлияет ваше обновление

Ответ 2

Псевдоним должен делать свое дело, если я правильно понимаю:

UPDATE test AS a
JOIN test AS b ON a.id = b.id
    SET a.name = 'New Name'
WHERE a.id = 104;

Это не работает для вас? ОБНОВЛЕНИЕ: это было проверено и работает на MySQL v5.6.

Ответ 3

Решение Hoyle, ANSI-SQL будет:

Update test
Set name = "United States of America"
Where FormId In (
                Select T1.FormID
                From test As T1
                Where T1.FieldName = 'city'
                    And T1.FieldValue = 'Houston'
                )
    And FieldName = 'country'

Я думаю, что вам не хватает, что вам нужно использовать псевдоним в таблице в подзапросе.