Deleting Rows From a Table Referenced in the Query

If you do a fair amount of SQL, then every now and then, you’ll like­ly run into a sit­u­a­tion where you need to delete some­thing from a table; but need to also ref­er­ence that table in the query as well.

The trou­ble is MySQL won’t let you do this, in many cir­cum­stances. For­tu­nate­ly, there is a sim­ple workaround.

delete from tableA 
 where id in (
   select b.id 
   from tableB b 
   join tableA a 
     on a.tableb_id = b.id
   );

The above query would throw an error sim­i­lar to the fol­low­ing:

ERROR 1093 (HY000): You can't specify target table 'tableA' for update in FROM clause

We can skirt this lim­i­ta­tion by nest­ing the sub­s­e­lect inside of anoth­er select state­ment. Then it will work just fine.

delete from tableA where id in (
  select aId from (
    select b.id as bId from tableB b join tableA a 
    on a.tableb_id = b.id 
  ) as apt
);

You’ll get out­put to indi­cate that the query is suc­cess­ful.

Query OK, 183 rows affected (0.53 sec)

This saved me a bunch of time and kept me from hav­ing to rework my query com­plete­ly, so I am shar­ing it with you!

Leave a Reply

Your email address will not be published. Required fields are marked *