MySQL UPDATE with SUBQUERY of same table

Question

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      | 

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

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" 

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

Answer 1

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

Answer 2

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.