MySQL UPDATE with SUBQUERY of same table

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

mysql sql phpmyadmin

Aliasing should do the trick, if I'm understanding correctly:

UPDATE test AS aJOIN 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.

CodeHunter