title: SQLAlchemy: Create delete query using self-join on MySQL
body: <p>I'm trying to replicate the following query in SQLAlchemy + MySQL without luck... </p>
  
  <pre><code>delete paths from paths
  join paths as p1 on p1.ancestor = &lt;ID&gt;
  where paths.descendant = p1.descendant;
  </code></pre>
  
  <p>SQLAlchemy seems be to ignoring any joins that I put into the delete query. I also tried using a subquery instead of a join, but that's not allowed in MySQL (can't select from same table as you want to delete from). Any help would be much appreciated.</p>
  
  <p>Update:
  Yes, I'm trying to use the ORM layer. Here's the queries that I've tried:</p>
  
  <pre><code>p1 = aliased(Path, name="p1")
  db.session.query(Path).join(
      p1, p1.ancestor==&lt;ID&gt;
  )
  .filter(
      Path.descendant==p1.Path.descendant
  ).delete()
  </code></pre>
  
  <p>And the subquery variant, but this doesn't work on MySQL, so it's of no use to me:</p>
  
  <pre><code>q = db.session.query(Path.descendant).filter(Path.ancestor==&lt;ID&gt;).subquery()
  db.session.query(Path).filter(Path.descendant.in_(q)).delete(synchronize_session='fetch')
  </code></pre>