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

The Hoyle, ANSI-SQL solution would be:

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'

The piece I think you are missing is that you need to use an alias on the table in the subquery.

Answer 3

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?