Help with DELETE query 4 unmatched records in 2 tables (1 Viewer)

[mkdrep]

Please see attached (jpg's). One shows the query code which when run displays the records in table [1Dodge Download Note T] that do NOT have a matching record in table [specjobs].

I assumed I could just convert this query to a DELETE query and it would DELETE the records that were pulled up from table [1Dodge Download Note T].

However, when that happens I get the error msg I have attached.

Can someone help me solve this problem? Thanks!

Mark

 

[arnelgp]

do you want to delete records from [1Dodge Download Notes T] table?
BACKUP your table before doing delete query, just to make sure.

DELETE FROM [1Dodge Download Notes T] LEFT JOIN [specjobs] ON [[1Dodge Download Notes T].DNJTNo = [specjobs].JobTrackNo WHERE [specjobs].JobTrackNo Is Null;

 

[mkdrep]

do you want to delete records from [1Dodge Download Notes T] table?
BACKUP your table before doing delete query, just to make sure.

DELETE FROM [1Dodge Download Notes T] LEFT JOIN [specjobs] ON [[1Dodge Download Notes T].DNJTNo = [specjobs].JobTrackNo WHERE [specjobs].JobTrackNo Is Null;

Thank you for your suggestion.

YES, I do want to delete any record from [1Dodge Download Notes T] that does not have a matching record in [specjobs]

I inserted your above code and still got the same error msg: "Specify the table containing the records you want to delete". Any other suggestions?

Also, when I initially ran the code you gave me, a "syntax error in JOIN operation" msg popped up. Turns out this issue was with [[1Dodge...had one too many "[" brackets! Oops........ Once I removed the extra "[" that msg disappeared..... and was replaced by "Specify the table containing the records you want to delete".

 

[Grumm]

Because when you have a join in the DELETE sql you need to specify the table.
What you need to do is this :
DELETE [1Dodge Download Notes T] FROM [1Dodge Download Notes T] LEFT JOIN [specjobs] ON [1Dodge Download Notes T].DNJTNo = [specjobs].JobTrackNo WHERE [specjobs].JobTrackNo Is Null;

If that still doesn't work, you would need to check only with exists in.

 

[mkdrep]

Because when you have a join in the DELETE sql you need to specify the table.
What you need to do is this :
DELETE [1Dodge Download Notes T] FROM [1Dodge Download Notes T] LEFT JOIN [specjobs] ON [1Dodge Download Notes T].DNJTNo = [specjobs].JobTrackNo WHERE [specjobs].JobTrackNo Is Null;

If that still doesn't work, you would need to check only with exists in.

I still get the same error msg :"Specify the table containing the records you want to delete".
Forgive my ignorance but I do not understand your second suggestion: "If that still doesn't work, you would need to check only with exists in". Could you expand on that further? Thank you.

 

[mkdrep]

Just as a follow up: I modified the Code to read:
DELETE [1Dodge Download Notes T].*
FROM [1Dodge Download Notes T] LEFT JOIN specjobs ON [1Dodge Download Notes T].DNJTNo=specjobs.JobTrackNo
WHERE [specjobs].JobTrackNo Is Null;

Once I added ".* " after DELETE [1Dodge Download Notes T] the query worked as I wanted it to.