2

What is the best way to delete the results of this selection from the table?

select id from taxon2 as a 
   where rank = 'No Taxon' and 
   (select count(*) from taxon2 as b 
      where a.id = b.parentid) = 0;
Serjik
  • 10,543
  • 8
  • 61
  • 70
xpda
  • 15,585
  • 8
  • 51
  • 82
  • possible duplicate of [How to delete from select in MySQL?](http://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql) – Chris Hayes Sep 24 '14 at 04:58
  • No, that answer does not apply (or work) here. The a. and b. in the selection require a different answer. – xpda Sep 24 '14 at 05:02

1 Answers1

4

Here is a solution with an OUTER JOIN:

delete taxon2 
from taxon2
  left join taxon2 t2 on taxon2.id = t2.parentid
where t2.id is null;

And with NOT EXISTS:

delete from taxon2
where rank = 'No Taxon' 
   and not exists (
      select 1
      from (select * from taxon2) as b 
      where b.parentid=taxon2.id)
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • @xpda -- glad that could help. now that I see your `rdbms`, I've include an `outer join` solution as well. – sgeddes Sep 24 '14 at 05:11