MySQL attempting to delete all rows which are not constrained by foreign key.

Question

Okay, this is (probably) a very simple question, but I am afraid I know almost no MySQL, so please put up with me. I'm just trying to delete every row from one table which is not constrained by a Foreign Key in another table - a specific table, there are only two tables involved here. The create statements look a bit like:

CREATE TABLE  `testschema`.`job` (
  `Job_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Job_Id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE  `ermieimporttest`.`jobassignment` (
  `JobAssignment_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `JobId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`JobAssignment_Id`),
  KEY `FK_jobassignment_1` (`JobId`),
  CONSTRAINT `FK_jobassignment_1` FOREIGN KEY (`JobId`) REFERENCES `job` (`Job_Id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Any my SQL statement is:

DELETE FROM job USING job INNER JOIN jobAssignment WHERE job.Job_Id != jobAssignment.JobId;

I thought this was correct - it should delete every job from the job table for which there does not exist a job assignment which has that job as it's Foreign Key. However, this fails with the following error when I try and execute it:

Cannot delete or update a parent row: a foreign key constraint fails (testdatabase.jobassignment, CONSTRAINT FK_jobassignment_1 FOREIGN KEY (JobId) REFERENCES job (Job_Id))

So what silly thing am I doing wrong?

EDIT: As usual, I found an answer only seconds after posting here. I used the (completely different) query:

DELETE FROM job WHERE Job_Id NOT IN (SELECT JobId FROM jobassignment) 

Out of curiosity, is this the better way to do it? Was my original idea even feasible? And if so, what was wrong with it?

Answer 1

DELETE FROM job USING job 
LEFT JOIN jobAssignment ON(job.Job_Id = jobAssignment.JobId)
WHERE jobAssignment.JobId IS NULL;

Answer 2

You'll probably need a subquery, not sure if this will work in mySQL, but something similar at least:

DELETE FROM job
WHERE job.Job_Id NOT IN (
  SELECT JobId FROM jobAssignment
)

Answer 3

Naktibalda suggests the subquery may be inefficient; if so you could try

DELETE FROM job
     WHERE NOT EXISTS (SELECT *
                           FROM jobassignment
                           WHERE job.Job_Id = jobassignment.Job_Id);

I've had bad experiences with IN and NOT IN in the past; less trouble with NOT EXISTS.