While *Delete* the multiple rows using column value, getting "update you can't specify target table for update in from clause" this error in mysql

user_16507386 0
Viewed 23

I'm using the below DELETE query.

DELETE FROM tableAA WHERE id IN (SELECT id FROM tableAA WHERE coreID IN (SELECT fill_ID FROM tableBBB) and columnData= 'data');

but I am getting this "update you can't specify target table for update in from clause" error.

mysql
2 Answers

Sorry, i can not check my example right now:

DELETE FROM tableAA LEFT JOIN tableAA.id = fill_ID.tableBBB WHERE fill_ID NOT IS NULL and columnData = data;
user_187280100

Try with this one:

DELETE FROM tableAA 
WHERE coreID IN (SELECT fill_ID FROM tableBBB)
  AND columnData = 'data';
user_124928900
Related