#1093 - You can't specify target table for update in FROM clause

Question

I wrote this delete for an application with mysql db and it doesn't work, I already search for answers but I cant find any kind error, someone can help me with a tip about this? thanks

1093 - You can't specify target table 'md1_acessos' for update in FROM clause

DELETE FROM md1_acessos
WHERE ID_USUARIO = 10
AND ID_UNIDADE not in ( 
    SELECT ID_UNIDADE 
    FROM md1_acessos
    WHERE ID_EMPRESA = 1
    AND ID_USUARIO = 1
    AND ID_MODULO IN (1,2,6)
);

See this for a workaround. http://verysimple.com/2011/03/30/mysql-cant-specify-target-table-for-update-in-from-clause/ — John Powell, Jun 10 '14 at 12:32
Or http://stackoverflow.com/questions/9285143/mysql-you-cant-specify-target-table-a-for-update-in-from-clause — John Powell, Jun 10 '14 at 12:33

score 0 · Answer 1 · answered Jun 10 '14 at 12:33

0

Try this:

DELETE FROM md1_acessos
WHERE ID_USUARIO = 10
AND NOT 
    (ID_EMPRESA = 1
    AND ID_USUARIO = 1
    AND ID_MODULO IN (1,2,6))

answered Jun 10 '14 at 12:33

Joe Taras

14,775
7
39
53

Where is ID_UNIDADE coming from? That is part of the where clause. – John Powell Jun 10 '14 at 12:36
ID_UNIDADE is under the condition with NOT because is the same table – Joe Taras Jun 10 '14 at 12:43
@acca90: Please post some data sample – Joe Taras Jun 10 '14 at 12:43

#1093 - You can't specify target table for update in FROM clause

1 Answers1