UnMatched Query to Delete Records

[Trilback]

I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.

DELETE Warranty
FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair]
WHERE (((Serials_Repair.Serials_Repair) Is Null));

I changed "Delete" it was "Select" thinking that would work but it does not. Also see screen shot.

 

[CJ_London]

Basically the error message is telling you that you have two tables and it cannot determine which record to delete.

Try this as an alternative

DELETE *
FROM Warranty
WHERE Exists(SELECT * FROM Serials_Repair WHERE Serials_Repair.Serials_Repair=Warranty.[Serial])=False

Just noted you have a field name the same as the table name - this will also cause issues, suggest change the field name to something else

 

[Trilback]

Basically the error message is telling you that you have two tables and it cannot determine which record to delete.

Try this as an alternative

DELETE *
FROM Warranty
WHERE Exists(SELECT * FROM Serials_Repair WHERE Serials_Repair.Serials_Repair=Warranty.[Serial])=False

Just noted you have a field name the same as the table name - this will also cause issues, suggest change the field name to something else

It worked but did not. I took a few serials from my list but the first one I did show in the list but a couple others I check did. I will take your second suggestion and see if that works if not I will just upload my Warranty Table as I made a backup before I ran it.

 

[Trilback]

It worked but did not. I took a few serials from my list but the first one I did show in the list but a couple others I check did. I will take your second suggestion and see if that works if not I will just upload my Warranty Table as I made a backup before I ran it.

Thanks for the help think I had a duplicate serial which I should not as I scan the serial of the machine at work. So hmmm. Will have to look at that when I get to work.