You can't specify target table 'cce_sa_fa' for update in FROM clause

This is the error that I received when tried to remove using the same table.

The following is the query that i have used to execute.

DELETE FROM `cce_sa_fa` WHERE id IN (SELECT cce_sa_fa.id FROM `cce_sa_fa` 
LEFT JOIN examinations ON examinations.id = cce_sa_fa.exam
WHERE `exam` IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,99,100,101,102,103,104,105,106,107,109,110,111,112,113,114,115,116,117,119,120,121,122,123,124,125,126,127,129,130,131,132,133,134,135,136,137,139,140,141,142,143,144,145,146,147,149,150,151,152,153,154,155,156,307,308,309) 
AND examinations.name != cce_sa_fa.name)

1 Answer 1


You can use JOIN in the FROM part instead of WHERE. This is the multi-table delete syntax, details in mysql docs: DELETESyntax.


DELETE fa.* 
FROM cce_sa_fa AS fa
  JOIN examinations AS ex 
    ON ex.id = fa.exam
WHERE fa.exam IN (1,2,...) 
  AND ex.name <> fa.name ;

Another way would be to convert the IN subquery to EXISTS:

FROM cce_sa_fa AS fa
      ( SELECT * 
        FROM examinations AS ex 
        WHERE ex.id = fa.exam
          AND ex.name <> fa.name 
WHERE fa.exam IN (1,2,...) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.