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

Question

I Tried

DELETE FROM installments
WHERE EXISTS (SELECT * FROM `installments`
              JOIN student
              ON installments.s_id=student.s_id
              WHERE installments.curr_sem=3 
                  AND student.bat_id=10 AND student.p_id=170 AND student.DeleteStatus=0);

but show error

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

Please help me

score 0 · Answer 1 · answered Mar 09 '16 at 09:13

0

Try this solution using DELETE JOIN:

DELETE 
    i
FROM 
    installments i JOIN student s USING (s_id)
WHERE 
    i.curr_sem=3 AND 
    s.bat_id=10 AND 
    s.p_id=170 AND 
    s.DeleteStatus=0

answered Mar 09 '16 at 09:13

Dylan Su

5,975
1
16
25

score 0 · Answer 2 · answered Mar 09 '16 at 09:39

The correct form for your statement is the following:

DELETE installments.*  
FROM installments 
JOIN student ON installments.s_id=student.s_id
WHERE installments.curr_sem=3 
AND student.bat_id=10 
AND student.p_id=170 
AND student.DeleteStatus=0;

If I understand it right, you want to delete only records in installments that match the join condition. You can delete or update records in one table with conditions on a join and there is no need for the nested select.

If you are working with Workbench you will have to disable safe mode or you can get an error in case you do not specify a key column in where.

Let me have your feedback

Regards

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

2 Answers2