MySQL UPDATE with SUBQUERY of same table
mysql, phpmyadmin, sql
Solution
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
Problem
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?