Simple Delete Query with Join Fails to execute (1 Viewer)

[Sebastapol]

I am banging my head against the wall with this one in frustration because the Delete query so simple but it will not execute with the error 'Specify the table containing the records you want to delete' each time I run it.

The select query based on the same works fine, but when I switch to a delete query I get the error.

Quite simply, I want to delete all records from Table1, where the query cannot find a match in Table2 based on a common IdNumber, as follows:

DELETE [Table1].*
FROM [Table1]
LEFT JOIN Table2 ON Table1.IdNumber = Table2.IdNumber
WHERE (((Table2.IdNumber) Is Null));

Can anyone see what I am doing wrong?

Thanks in advance

 

[arnelgp]

DELETE [Table1].*
FROM [Table1]
WHERE IdNumber NOT IN (SELECT idNumber FROM Table2);

 

[The_Doc_Man]

First, you cannot equate null to anything. This structure would require Table1.IdNumber to also be null (based on the equals-sign) BUT there is this little "gotcha" about nulls. Not only are they never equal to any value, they are not even equal to each other.

...ON Table1.IdNumber = Table2.IdNumber
WHERE (((Table2.IdNumber) Is Null));

What is it that you are trying to do here - in words, not code?

EDIT: arnelgp's suggestion is close - but if that "ON" clause would have worked, you would not have needed Table2 to begin with, because IF the ON would have made logical sense, you could have selected from Table1 where ITS IdNumber was null. So what is it that you are really trying to do?

 

[theDBguy]

Hi. Welcome to AWF!

Just curious, you could also try adding the DISTINCTROW keyword to see if it will help.

 

[isladogs]

@The_Doc_Man
Sorry to disagree with your answer but, assuming the IDNumber fields are primary key fields, the delete query in post #1 is perfectly valid and should work as written. That is the SQL obtained if you use the unmatched query wizard and then change it to a DELETE query.
If you try it, you will be able to see for yourself that it works. In fact its one of the methods used in my article on Synchronising Data

However changing it to DELETE DISTINCTROW ... should solve any issues with duplicated values where those aren't PK fields.

@Sebastapol
Try removing the [] brackets. They aren't needed unless your table names contain spaces or special characters.

 

[Sebastapol]

DELETE [Table1].*
FROM [Table1]
WHERE IdNumber NOT IN (SELECT idNumber FROM Table2);

Thanks @arnelgp this works for me. Still not sure why the other syntax wouldn't execute for me though.

 

[Sebastapol]

Thanks all, I tried DISTINCTROW but no joy. IdNumber is the primary key on Table1 but not on Table2 which would have duplicates. So maybe this is the issue.

@arnelgp solution works however...

Still, it seems odd the select query would pull back results but the delete query did not want to execute

 

[isladogs]

I just checked your exact setup.
it doesn't matter that IDNumber isn't the primary key in Table2 as that's not the table you are deleting from.
However because you have duplicates, you will get the error message cannot delete from specified tables .
Using DISTINCTROW solves that issue as it ensures unique records. It should have worked unless there are other complications you haven't mentioned

The NOT IN ...approach is also a valid method. As it contains a subquery it would be slightly slower but i doubt you would notice unless you have a large number of records in each table.

Another similar variation uses NOT EXISTS instead. If interested, see section 7 of my Synchronising Data article

 

[The_Doc_Man]

Colin, my objection was primarily based on the idea that the OP's ON clause looked for equality between corresponding ID fields in the two tables but the WHERE clause in the same overall SQL statement specified that one of those ID fields was NULL. You can't equate NULLs. You know that. All you do when comparing NULLs is you propagate NULL.

Are you going to tell me that in the formal sense for VBA, that ( NULL = NULL ) = TRUE? Because that is a huge no-no, not to mention that it is also... well, I would say FALSE - but in fact, it is NULL. When you do the comparison, you don't get back TRUE or FALSE. You get back NULL. The whole point of the predicate of the ON clause is that you do the join based on TRUE predicates .... ON table1.A = table2.APrime ... but that expression following the ON has to return TRUE, not NULL, in order to return anything.

Also, can an index or PK even contain a NULL? I remember some discussion in the forum around the idea that an index, unique or not and primary or not, cannot be null. So I looked it up.

Index or primary key cannot contain a Null value (Error 3058)

Office VBA reference topic

docs.microsoft.com

I stand on my idea. If you consider the ON clause and the WHERE clause together, that combination can't work because the predicates will return NULL, not TRUE (or FALSE). No records will be returned.

 

[Pat Hartman]

In a query that joins two tables, delete, deletes only rows from the CHILD table. It does not delete rows from the parent table. That is why you need to use a subselect if you want to delete Parent rows but the criteria involves the child table.

 

[isladogs]

@The_Doc_Man
OK, lets take a simple example which is deliberately similar to the example given by the OP

Table1
ID - autonumber PK
Field1 - number

Table2
T2_ID - autonumber PK
ID - number - FK - can contain duplicates & nulls
Field1 - number

A simple unmatched SELECT query using the wizard:

SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null;

A simple DELETE query based on the above to delete records from the 'parent table' Table1:

DELETE Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null;

This fails due to duplicate values in the ID field of the child table

For the delete query to work, we must specify unique records:

DELETE DISTINCTROW Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null;

This does work:

The query deletes the 5 records in table 1 that aren't in Table2
The fact that there are both duplicate ID values & nulls in the child table Table2 is irrelevant here as we aren't deleting records from Table2

@Pat Hartman
Apologies if I'm being dense, but I'm not sure whether your response is supporting the above. You appear to me to be stating the opposite

 

[The_Doc_Man]

Colin, you are overthinking it. Read the original SQL again. I'm not interested in your "similar" example, even though what you said about how to do JOINs is perfectly true. READ THE ORIGINAL SYNTAX LITERALLY. That is what I was responding to. Not the general method but the specific syntax as presented in the original question.

An ON table1.IdNumber = table2.IdNumber clause works when the predicate {table1.IdNumber = table2.IdNumber} is TRUE for some records. Can we agree on that? The JOIN will not offer records for which the ON clause's predicate is not TRUE. If no combination of records exhibits that equality, the returned recordset is the Empty Set. Do we also agree on that?

In the SQL as originally written in post #1, there was also a WHERE clause involving one of the two elements of the ON clause in the same statement. Of necessity, that means the two clauses (ON and WHERE) must be referencing the same field. That follows from basic syntax rules for SQL.

That WHERE clause would only select records where table2.IdNumber IS NULL - which, by implied association through the ON clause also referencing table2.IdNumber, means the OP was asking for table1.IdNumber to be null so as to obtain JOINed records where the ID fields would match, i.e. BOTH be NULL.

Problem is, an ON clause doesn't work for NULLs because you can never get a TRUE out of that predicate's comparison expression. The NULL in ANY expression ALWAYS propagates that NULL "outward" and therefore what you get back from the given comparison is never TRUE under any circumstances. In this case, it is always NULL. And that means you would get back an empty recordset.

The SQL statement was essentially an anti-tautology. A "real" tautology (like 1=1) is ALWAYS true; an anti-tautology (like 0 = null) is NEVER true. The original question was "why doesn't this query work?" I was answering that question. The query selected no records. It would NEVER select records as originally written.

@Sebastapol - I apologize that we have exposed a disagreement in trying to answer your question. This happens when experienced people get together and look at things through the filter of their past different experiences. You have done nothing wrong in asking the question. You just happened to pick a topic where this kind of difference could pop up. Once the discussion settles down, we might be able to offer more help.

In a way, this post DOES address why your DELETE query didn't do as you thought it should.

 

[isladogs]

Doc
In my opinion, I was definitely not overthinking it (though I may well be about to do so).
I believe your arguments are flawed

However, yes of course I agree with the following:

An ON table1.IdNumber = table2.IdNumber clause works when the predicate {table1.IdNumber = table2.IdNumber} is TRUE for some records. Can we agree on that? The JOIN will not offer records for which the ON clause's predicate is not TRUE. If no combination of records exhibits that equality, the returned recordset is the Empty Set. Do we also agree on that?

Let me start by agreeing other common ground
1. Two nulls cannot ever be equal to each other
2. Primary key fields cannot contain null values or duplicate values

From there onwards, we diverge:

Also, can an index or PK even contain a NULL? I remember some discussion in the forum around the idea that an index, unique or not and primary or not, cannot be null. So I looked it up.

Index or primary key cannot contain a Null value (Error 3058)

In fact, an indexed (non PK) field CAN contain nulls whether set to allow duplicates or set to have no duplicates.
It can contain multiple nulls for both types of index precisely because no two nulls will ever be equal.

For example, the ID field is indexed with no duplicates allowed.

So it won't allow record 6 as its a duplicate. However the two null values do NOT flout index rules.

I stand on my idea. If you consider the ON clause and the WHERE clause together, that combination can't work because the predicates will return NULL, not TRUE (or FALSE). No records will be returned.

That is Incorrect as already demonstrated. As already stated, it is used for SELECT queries in the unmatched query wizard. It works.
To be explicit it works whether or not the joined fields are both primary keys, only one is a PK or even where neither are a PK
As a deliberately OTT example, look at the following:

Table6 is a direct copy of Table5 complete with indexed ID field and 4 null values. T2_ID are the autonumber PK fields
The unmatched query displays those 4 records in Table5 with null values ....because of course being nulls they are not equal to the equivalent records in Table6.

The unmatched query SQL is

SELECT Table5.*
FROM Table5 LEFT JOIN Table6 ON Table5.ID = Table6.ID
WHERE Table6.ID Is Null;

Converting that to a delete query will delete those 4 records with null values:

DELETE DISTINCTROW Table5.*
FROM Table5 LEFT JOIN Table6 ON Table5.ID = Table6.ID
WHERE Table6.ID Is Null;

The examples in my last post were identical in structure to that described by the OP.

The first DELETE query I used in post #11 was exactly the same as the code used by the OP in post #1
To repeat, that query works providing both ID fields are primary key fields.
Where that is the case, the NOT IN code given by @arnelgp in post #2 also works.

Later on in post #7, the OP stated that the ID field on Table2 wasn't a PK field and could contain duplicates.
In that situation, @arnel's code returns no records.
The original DELETE query will however work PROVIDED it is changed to Unique records (DISTINCTROW)

Hopefully that covers everything. Updated copy of sample db attached

 

[The_Doc_Man]

Colin, I am going to withdraw from this in a moment. I like you too much to get involved in a long, drawn out discussion that might become intense and is at least in part diverging from the original question.

I state simply that I was directly answering the original "why didn't this work" question and nothing else. You have seen me come back in other cases and, rather than suggest yet another solution, to answer the "WHY" portion of the original question. It is my style. In this case the discussion has unnecessarily escalated.

Our OP sebastapol has a need for an answer to make progress. Other people are giving that. You and I don't need to argue because at this point it would be noise and distraction.

 

[isladogs]

I'm happy to leave this discussion as well though it would have been good to have reached a consensus.

For info, the OP stated he/she had a working solution back in posts #6 & #7 even though my tests on the same scenario as he/she described indicated that solution shouldn't have worked. Perhaps there's something else of relevance that the OP didn't mention

 

[The_Doc_Man]

Perhaps there's something else of relevance that the OP didn't mention

Gosh, when has THAT ever happened on this forum?