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 qryDateSSNSingl eContract INNER JOIN [Data With Import Date
Current] ON (qryDateSSNSing leContract.[Owner SSN] = [Data With Import
Date Current].[Owner SSN]) AND (qryDateSSNSing leContract.[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 qryDateSSNSingl eContract INNER JOIN [Data With Import Date
Current] ON (qryDateSSNSing leContract.[Owner SSN] = [Data With Import
Date Current].[Owner SSN]) AND (qryDateSSNSing leContract.[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 qryDateSSNSingl eContract.[Owner SSN],
qryDateSSNSingl eContract.[Transaction Date]
WHERE (qryDateSSNSing leContract.[Owner SSN]
= [Data With Import Date Current].[Owner SSN])
AND (qryDateSSNSing leContract.[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 qryDateSSNSingl eContract.* INTO zzz_temp_tbl
FROM qryDateSSNSingl eContract;

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]);