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 (
   from tableB b 
   join tableA a 
     on a.tableb_id =

The above query would throw an error sim­i­lar to the following:

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 as bId from tableB b join tableA a 
    on a.tableb_id = 
  ) as apt

You’ll get out­put to indi­cate that the query is successful.

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!

[amazon_link asins=‘059652708X,1449314287,0321833872,1449374026,1449312004’ template=‘ProductCarousel’ store=‘openmindspace-20′ marketplace=‘US’ link_id=‘7470313a-d376-11e6-91d4-75644fc73d90’]

Leave a Reply

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