1

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

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

Example:

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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