title: MySQL Getting around error 1093
body: <p>Error 1093 states that you can't UPDATE or DELETE using a subquery if your subquery queries the table you are deleting from.</p>
  
  <p>So you can't do</p>
  
  <pre><code>delete from table1 where id in (select something from table1 where condition) ;
  </code></pre>
  
  <p>Ok, what's the best way to work around that restriction, (assuming you really do need to subquery to perform the delete and cannot eliminate the self referencing subquery entirely?)</p>
  
  <p>Edit:</p>
  
  <p>Here's the query for those who are interested:</p>
  
  <pre>
  mysql> desc adjacencies ;
  +---------+---------+------+-----+---------+-------+
  | Field   | Type    | Null | Key | Default | Extra |
  +---------+---------+------+-----+---------+-------+
  | parent  | int(11) | NO   | PRI | NULL    |       |
  | child   | int(11) | NO   | PRI | NULL    |       |
  | pathLen | int(11) | NO   |     | NULL    |       |
  +---------+---------+------+-----+---------+-------+
  
  
  
  -- The query is going to
  -- tell all my children to
  -- stop thinking my old parents
  -- are still their parents
  
  delete from adjacencies
  where parent in 
  (
  -- ALL MY PARENTS,grandparents
    select parent
    from adjacencies
    where child=@me
    and parent!=@me
  )
  
  -- only concerns the relations of my
  -- grandparents WHERE MY CHILDREN ARE CONCERNED
  and child in
  (
    -- get all my children
    select child
    from adjacencies
    where parent=@me
  )
  
  ;
  </pre>
  
  <p>So what I've tried so far is creating a temporary table called <code>adjsToDelete</code></p>
  
  <pre><code>create temporary table adjsToRemove( parent int, child int ) ;
  insert into adjsToRemove...
  </code></pre>
  
  <p>So now I have a collection of relations to delete, where the parent/child pairs each uniquely identify a row to delete.  But <em>how do I delete each <strong>pair</em></strong> from the adjacencies table now?</p>
  
  <p>It seems I need to add a unique <code>auto_increment</code>ed key to each entry in <code>adjacencies</code>, is that right?</p>