If you do a fair amount of SQL, then every now and then, you’ll likely run into a situation where you need to delete something from a table; but need to also reference that table in the query as well.

The trouble is MySQL won’t let you do this, in many circumstances. Fortunately, there is a simple 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 similar to the following:

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

We can skirt this limitation by nesting the subselect inside of another select statement. 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 output to indicate that the query is successful.

Query OK, 183 rows affected (0.53 sec)

This saved me a bunch of time and kept me from having to rework my query completely, so I am sharing 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′]