MySQL tries to delete all rows that are not limited by a foreign key

Question

Well, this is (maybe) a simple question very much , but I'm afraid I hardly know MySQL, so please put up with me. I'm just trying to delete every row from one table, which is not limited by a foreign key in another table - a specific table, only two tables are involved here. The create statements are 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:

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

I thought that was right - he should delete every task from the task table for which there is no task that has this task as a foreign key. However, when you try to complete this, the following error fails:

Cannot delete or update the parent row: foreign key constraint is not satisfied ( testdatabase . jobassignment CONSTRAINT FK_jobassignment_1 FOREIGN KEY ( JobId ) LINKS job ( Job_Id ))

So what nonsense am I doing wrong?

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

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

Out of curiosity, is this the best way to do this? Was my original idea workable? And if so, what happened?

Answer 1

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

Answer 2

You will 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 that a subquery may be inefficient; if so you can try

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

I had bad experiences with IN and NOT IN in the past; fewer problems with NOT EXISTS.