SQL delete syntax

[chelm]

I wrote the following in queries:

DELETE * 
FROM tblCitationAuthor 
WHERE (((tblCitationAuthor.CitationAuthorID) Not In (SELECT CitationAuthorLastName from tblCitations)));

The field and table names are correct but it doesn't delete the records, I get no errors either. Am I doing something wrong?

 

[RuralGuy]

What method are you using to execute this Delete query?

 

[chelm]

Well I created it by doing a query, design view, SQL view and then writing the code. After I created the query it shows in the list as a delete query I've tried clicking it and I've tried running it via a macro attached to a form button. Using the exact same logic I've gotten the delete query to work on a couple of other tables, but it just doesn't work on this table.

I'm wondering if it has something to do with the name mismatch? Do I need to rename the fields so they are the same?

 

[RuralGuy]

What version of Access and what OS are you using?

 

[chelm]

Access 2007 on Windows XP.

To be clear these work:

DELETE * 
FROM tblCitations 
WHERE (((tblCitations.CitationID) Not In (SELECT CitationID from tblDisserationToCitationJoin)));

DELETE * 
FROM tblCitationTitles 
WHERE (((tblCitationTitles.CitationTitleID) Not In (SELECT CitationTitle from tblCitations)));

And these don't:

DELETE * 
FROM tblCitationAuthor 
WHERE (((tblCitationAuthor.CitationAuthorID) Not In (SELECT CitationAuthorLastName from tblCitations)));

DELETE * 
FROM tblCitationYears 
WHERE (((tblCitationYears.CitationYearID) Not In (SELECT CitationYear from tblCitations)));

DELETE * 
FROM tblCitationType 
WHERE (((tblCitationType.CitationTypeID) Not In (SELECT CitationType from tblCitations)));

 

[RuralGuy]

I suspect you might be using lookup fields.

 

[chelm]

I removed all lookup fields and boblarson's and your urging:
http://www.access-programmers.co.uk/forums/showthread.php?t=144250

So I don't think that should be a problem anymore. I tried changing to the only other field and i get a type mismatch sql error.

Could this have anything to do with the fields:
CitationAuthorLastName from tblCitations
CitationYear from tblCitations

having null or blank values in some records?

 

[RuralGuy]

Here's the fields that need to be the same type. Are they?
tblCitationAuthor.CitationAuthorID vs tblCitations.CitationAuthorLastName
tblCitationYears.CitationYearID vs tblCitations.CitationYear
tblCitationType.CitationTypeID vs tblCitations.CitationType

 

[chelm]

Yes they are the same type, they are both integers in all pairs.

So for example this query works:

SELECT tblCitationAuthor.CitationAuthorID, tblCitationAuthor.CitationAuthorLastName
FROM tblCitationAuthor LEFT JOIN tblCitations ON tblCitationAuthor.CitationAuthorID = tblCitations.CitationAuthorLastName
WHERE (((tblCitations.CitationAuthorLastName) Is Null));

I use it to see what records are not used...

 

[RuralGuy]

Good! In that case you need to protect against Nulls with the Nz() function.

 

[chelm]

I've never used it before, I'll look it up and see if I can figure out how to do it.

Thanks

 

[RuralGuy]

Don't hesitate to post back if you still need assistance.

 

[chelm]

I updated the queries with the following syntax and they seem to work. I just want to make sure it is correct. If you see any issues please let me know.

DELETE tblCitationAuthor.CitationAuthorID, *
FROM tblCitationAuthor
WHERE (((tblCitationAuthor.CitationAuthorID) Not In (SELECT Nz(CitationAuthorLastName,1) from tblCitations)));

 

[RuralGuy]

Did you want a NUll to return a one or a zero?
Nz(CitationAuthorLastName,0)

 

[chelm]

I guess 0 is probably better, just in case 1 isn't used and should be deleted...

Thanks for all the help.

CH

 

[RuralGuy]

You're welcome.