Delete query is not working

[Guest]

Hi,

I am trying to create a delete query that cleans up a table based on the
latest data from a query. When I run the query (not the delete query) I can
delete any record in the query. I can also delete any record in the table.
However, when I create my delete query by combining the table and query, I am
told the result is read only and therefore, my delete query fails.

Here is the SQL:

DELETE DISTINCTROW tblExempt.*
FROM tblExempt LEFT JOIN [qryMedical_Dental(NonCurrent)] ON tblExempt.SSN =
[qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE ((([qryMedical_Dental(NonCurrent)].LO_SSAN) Is Null));

A little more background on what I am attempting to do. The query is a "bad
boy" list. However, some have valid reasons and I want to exempt them from
showing in my reports. So, I have an update query (based on the
qryMedical_Dental(NonCurrent)) that adds everyone to the tblExempt. The
tblExempt also has a Yes/No field which is what I use to exempt them from
showing in my reports. Each week the data can change. So I want to remove
any records from the tblExempt that are no longer valid (no longer on the
"bad boy" list). Deleting all the records and replacing with the latest "bad
boy" list is not an option as I will lose the data on those I have exempted.
If I create a table based on this weeks data and then run a delete query
based on the two tables (this weeks data and tblExempt), I can then remove
the "orphaned" records from tblExempt. However, I would like to be able to
avoid having to create a new table.

Any help would be much appreciated.

Thanks

Burnsie

 

[John Spencer]

Try rewriting the query as follows.

DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN NOT IN
(SELECT LO_SSAN
FROM [qryMedical_Dental(NonCurrent)] )

Usual advice applies. BACKUP your data first, just in case this doesn't
work the way you expect.

You might also try the following - which if it works, should be faster
DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN IN
(SELECT Tmp.SSN
FROM tblExempt as Tmp LEFT JOIN [qryMedical_Dental(NonCurrent)]
ON Tmp.SSN = [qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE [qryMedical_Dental(NonCurrent)].LO_SSAN Is Null)

 

[Guest]

John,

Both worked but, as you said, the second one is much much much faster. To
further my understanding of queries, can you explain why your SQLs worked and
my didn't. And why the second SQL runs much faster? I notice that you spend
a lot of time helping others and if you don't have time for this, it is quite
understandable.

Anyway, thanks for the help.

Burnsie

Try rewriting the query as follows.

DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN NOT IN
(SELECT LO_SSAN
FROM [qryMedical_Dental(NonCurrent)] )

Usual advice applies. BACKUP your data first, just in case this doesn't
work the way you expect.

You might also try the following - which if it works, should be faster
DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN IN
(SELECT Tmp.SSN
FROM tblExempt as Tmp LEFT JOIN [qryMedical_Dental(NonCurrent)]
ON Tmp.SSN = [qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE [qryMedical_Dental(NonCurrent)].LO_SSAN Is Null)

Hi,

I am trying to create a delete query that cleans up a table based on the
latest data from a query. When I run the query (not the delete query) I
can
delete any record in the query. I can also delete any record in the
table.
However, when I create my delete query by combining the table and query, I
am
told the result is read only and therefore, my delete query fails.

Here is the SQL:

DELETE DISTINCTROW tblExempt.*
FROM tblExempt LEFT JOIN [qryMedical_Dental(NonCurrent)] ON tblExempt.SSN
=
[qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE ((([qryMedical_Dental(NonCurrent)].LO_SSAN) Is Null));

A little more background on what I am attempting to do. The query is a
"bad
boy" list. However, some have valid reasons and I want to exempt them
from
showing in my reports. So, I have an update query (based on the
qryMedical_Dental(NonCurrent)) that adds everyone to the tblExempt. The
tblExempt also has a Yes/No field which is what I use to exempt them from
showing in my reports. Each week the data can change. So I want to
remove
any records from the tblExempt that are no longer valid (no longer on the
"bad boy" list). Deleting all the records and replacing with the latest
"bad
boy" list is not an option as I will lose the data on those I have
exempted.
If I create a table based on this weeks data and then run a delete query
based on the two tables (this weeks data and tblExempt), I can then remove
the "orphaned" records from tblExempt. However, I would like to be able
to
avoid having to create a new table.

Any help would be much appreciated.

Thanks

Burnsie

 

[John Spencer]

The second is faster because NOT IN is much slower than IN. It's just
harder to prove something is not there than it is to prove something is
there.

I don't know why your original did not work. It could have been that you
didn't have any primary key field showing up in the "stacked" query.

John,

Both worked but, as you said, the second one is much much much faster. To
further my understanding of queries, can you explain why your SQLs worked
and
my didn't. And why the second SQL runs much faster? I notice that you
spend
a lot of time helping others and if you don't have time for this, it is
quite
understandable.

Anyway, thanks for the help.

Burnsie

Try rewriting the query as follows.

DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN NOT IN
(SELECT LO_SSAN
FROM [qryMedical_Dental(NonCurrent)] )

Usual advice applies. BACKUP your data first, just in case this doesn't
work the way you expect.

You might also try the following - which if it works, should be faster
DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN IN
(SELECT Tmp.SSN
FROM tblExempt as Tmp LEFT JOIN [qryMedical_Dental(NonCurrent)]
ON Tmp.SSN = [qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE [qryMedical_Dental(NonCurrent)].LO_SSAN Is Null)

Hi,

I am trying to create a delete query that cleans up a table based on
the
latest data from a query. When I run the query (not the delete query)
I
can
delete any record in the query. I can also delete any record in the
table.
However, when I create my delete query by combining the table and
query, I
am
told the result is read only and therefore, my delete query fails.

Here is the SQL:

DELETE DISTINCTROW tblExempt.*
FROM tblExempt LEFT JOIN [qryMedical_Dental(NonCurrent)] ON
tblExempt.SSN
=
[qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE ((([qryMedical_Dental(NonCurrent)].LO_SSAN) Is Null));

A little more background on what I am attempting to do. The query is a
"bad
boy" list. However, some have valid reasons and I want to exempt them
from
showing in my reports. So, I have an update query (based on the
qryMedical_Dental(NonCurrent)) that adds everyone to the tblExempt.
The
tblExempt also has a Yes/No field which is what I use to exempt them
from
showing in my reports. Each week the data can change. So I want to
remove
any records from the tblExempt that are no longer valid (no longer on
the
"bad boy" list). Deleting all the records and replacing with the
latest
"bad
boy" list is not an option as I will lose the data on those I have
exempted.
If I create a table based on this weeks data and then run a delete
query
based on the two tables (this weeks data and tblExempt), I can then
remove
the "orphaned" records from tblExempt. However, I would like to be
able
to
avoid having to create a new table.

Any help would be much appreciated.

Thanks

Burnsie

 

[Guest]

John,

Thanks so much. This sure helps my understanding.

Burnsie

The second is faster because NOT IN is much slower than IN. It's just
harder to prove something is not there than it is to prove something is
there.

I don't know why your original did not work. It could have been that you
didn't have any primary key field showing up in the "stacked" query.

John,

Both worked but, as you said, the second one is much much much faster. To
further my understanding of queries, can you explain why your SQLs worked
and
my didn't. And why the second SQL runs much faster? I notice that you
spend
a lot of time helping others and if you don't have time for this, it is
quite
understandable.

Anyway, thanks for the help.

Burnsie

Try rewriting the query as follows.

DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN NOT IN
(SELECT LO_SSAN
FROM [qryMedical_Dental(NonCurrent)] )

Usual advice applies. BACKUP your data first, just in case this doesn't
work the way you expect.

You might also try the following - which if it works, should be faster
DELETE DISTINCTROW tblExempt.SSN
FROM tblExempt
WHERE SSN IN
(SELECT Tmp.SSN
FROM tblExempt as Tmp LEFT JOIN [qryMedical_Dental(NonCurrent)]
ON Tmp.SSN = [qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE [qryMedical_Dental(NonCurrent)].LO_SSAN Is Null)


Hi,

I am trying to create a delete query that cleans up a table based on
the
latest data from a query. When I run the query (not the delete query)
I
can
delete any record in the query. I can also delete any record in the
table.
However, when I create my delete query by combining the table and
query, I
am
told the result is read only and therefore, my delete query fails.

Here is the SQL:

DELETE DISTINCTROW tblExempt.*
FROM tblExempt LEFT JOIN [qryMedical_Dental(NonCurrent)] ON
tblExempt.SSN
=
[qryMedical_Dental(NonCurrent)].LO_SSAN
WHERE ((([qryMedical_Dental(NonCurrent)].LO_SSAN) Is Null));

A little more background on what I am attempting to do. The query is a
"bad
boy" list. However, some have valid reasons and I want to exempt them
from
showing in my reports. So, I have an update query (based on the
qryMedical_Dental(NonCurrent)) that adds everyone to the tblExempt.
The
tblExempt also has a Yes/No field which is what I use to exempt them
from
showing in my reports. Each week the data can change. So I want to
remove
any records from the tblExempt that are no longer valid (no longer on
the
"bad boy" list). Deleting all the records and replacing with the
latest
"bad
boy" list is not an option as I will lose the data on those I have
exempted.
If I create a table based on this weeks data and then run a delete
query
based on the two tables (this weeks data and tblExempt), I can then
remove
the "orphaned" records from tblExempt. However, I would like to be
able
to
avoid having to create a new table.

Any help would be much appreciated.

Thanks

Burnsie