DELETE Statement question

[Matt]

I am writing a DELETE statement and I want to filter the records using
another SELECT statement.

My SELECT statement is a GROUP BY query that grabs all social security
numbers from the "Data With Import Date Current" table on a given day
where there was only one transaction (count of SSN = 1). I want to
delete these records from the "Data With Import Date Current" table.
I would like to do this by joining the "Data With Import Date Current"
table to the SELECT statement in a delete query (joining on date and
SSN), but I get an error "Could Not Delete From The Specified Table"
when I try this. So to get around it I used a sub query. This takes
FOREVER and I was wondering if there was a way I could join these 2
queries in a delete query so it runs faster.
Here is my SQL (The one that is giving me the error):
---------------------------------------------------------------------------*------------------------------

DELETE [Data With Import Date Current].*
FROM qryDateSSNSingleContract INNER JOIN [Data With Import Date
Current] ON (qryDateSSNSingleContract.[Owner SSN] = [Data With Import
Date Current].[Owner SSN]) AND (qryDateSSNSingleContract.[Transaction
Date] = [Data With Import Date Current].[Transaction Date]);
---------------------------------------------------------------------------*-------------------------------

As always, thanks in advance!

[Gord]

Matt wrote:

I am writing a DELETE statement and I want to filter the records using
another SELECT statement.

My SELECT statement is a GROUP BY query that grabs all social security
numbers from the "Data With Import Date Current" table on a given day
where there was only one transaction (count of SSN = 1). I want to
delete these records from the "Data With Import Date Current" table.
I would like to do this by joining the "Data With Import Date Current"
table to the SELECT statement in a delete query (joining on date and
SSN), but I get an error "Could Not Delete From The Specified Table"
when I try this. So to get around it I used a sub query. This takes
FOREVER and I was wondering if there was a way I could join these 2
queries in a delete query so it runs faster.
Here is my SQL (The one that is giving me the error):
---------------------------------------------------------------------------*------------------------------

DELETE [Data With Import Date Current].*
FROM qryDateSSNSingleContract INNER JOIN [Data With Import Date
Current] ON (qryDateSSNSingleContract.[Owner SSN] = [Data With Import
Date Current].[Owner SSN]) AND (qryDateSSNSingleContract.[Transaction
Date] = [Data With Import Date Current].[Transaction Date]);
---------------------------------------------------------------------------*-------------------------------

DELETE and UPDATE queries must be run against "updateable" recordsets.
When you join your table to the aggregate (GROUP BY) query the
resultant recordset is not "updateable" and your delete fails even
though you are not trying to delete anything from the query.

So, you either need to use a subquery, something like this (untested)
DELETE [Data With Import Date Current].*
FROM [Data With Import Date Current]
WHERE EXISTS (
SELECT qryDateSSNSingleContract.[Owner SSN],
qryDateSSNSingleContract.[Transaction Date]
WHERE (qryDateSSNSingleContract.[Owner SSN]
= [Data With Import Date Current].[Owner SSN])
AND (qryDateSSNSingleContract.[Transaction Date]
= [Data With Import Date Current].[Transaction Date])
);
or send the results of your aggregate query to a temporary table and
then use that table for your join in the DELETE query, i.e.
SELECT qryDateSSNSingleContract.* INTO zzz_temp_tbl
FROM qryDateSSNSingleContract;

DELETE [Data With Import Date Current].*
FROM zzz_temp_tbl INNER JOIN [Data With Import Date
Current] ON (zzz_temp_tbl.[Owner SSN] = [Data With Import
Date Current].[Owner SSN]) AND (zzz_temp_tbl.[Transaction
Date] = [Data With Import Date Current].[Transaction Date]);