How do I efficiently delete all rows in a many-to-many relationship table that have a given destination exclusively on one side of the relationship?

I have a MySQL database with a relationship table which can be simplified to:

CREATE TABLE `Paths` (
  `origin` char(32) NOT NULL,
  `destination` char(32) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`),
)

This is a many-to-many relationship. I want to delete all paths where all paths from that origin only lead to a certain place, e.g. Neverland.

This 开发者_如何学JAVASQL should work:

DELETE FROM Paths WHERE origin IN (SELECT DISTINCT origin FROM Paths WHERE destination = 'Neverland') AND origin NOT IN (SELECT DISTINCT origin FROM Paths WHERE destination <> 'Neverland');

But, is there a better, more efficient way?

Thanks!

Try:

  Delete Paths
  From Paths p
  Where Not Exists  
      (Select * From paths 
       Where Origin = p.origin
       And Destination <> 'Neverland')

This is improved:

DELETE FROM Paths WHERE destination= 'Neverland' AND origin NOT IN (SELECT origin FROM Paths WHERE branchto != 'Neverland');

But I still think it can be done better.

Try this - it's at least close.

DELETE Paths
FROM Paths
JOIN Paths AS pd
    ON Paths.origin = pd.destination
WHERE Paths.origin = 'Neverland'

This is the second syntax on http://dev.mysql.com/doc/refman/5.0/en/delete.html