ERROR 1093 (HY000): You can’t specify target table ‘x’ for update in FROM clause

MySQL doesnt allow you to use a subquery in a delete cause with the same table name, such as:

DELETE a.*
FROM myTable a
WHERE id IN (
     SELECT id
     FROM myTable
     WHEREfoo = 'bar'
)

A workaround (create a derived table – then put your sub-query inside):

DELETE a.*
FROM myTable a
WHERE id IN (
     SELECT id FROM (
          SELECT id
          FROM myTable
          WHEREfoo = 'bar'
     ) as dTable
)

MySQL Enum field not ordering correctly

Thats because: It will order in the order the enum values are defined at table level – not the actual varchar/int/etc value.

Fix:

SELECT CAST(a.enumfield AS CHAR(255)) AS enumfield, a.*
FROM `mytable` a
ORDER BY CAST(a.enumfield AS CHAR(255)) ASC

Tip: change it to CAST X as INT – if you’ve got integers, so on and so forth.

Tip2: use table aliases