468,294 Members | 1,888 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,294 developers. It's quick & easy.

DELETE Statement question

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!

Jan 21 '07 #1
1 3309

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

Jan 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

52 posts views Thread by Newsnet Customer | last post: by
1 post views Thread by Matik | last post: by
4 posts views Thread by Stefan Strasser | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
16 posts views Thread by robert | last post: by
12 posts views Thread by yufufi | last post: by
29 posts views Thread by =?Utf-8?B?R2Vvcmdl?= | last post: by
8 posts views Thread by Michel Esber | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.