REPOST: SQL 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!

 

[Ken Snell \(MVP\)]

May work if you add DISTINCT:

DELETE DISTINCT [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]);

Or, perhaps this:

DELETE DISTINCT [Data With Import Date Current].*
FROM [Data With Import Date Current]
WHERE EXISTS
(SELECT qryDateSSNSingleContract .[Owner SSN]
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]));

--

Ken Snell
<MS ACCESS MVP>



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!

 

[Albert D. Kallal]

Hum...no answers...obviously no one been able to get a "join" query to work
when you delete.

I don't think you can use a join when you delete with jet sql.

I would suggest that you still try the sub-query idea. The sub-query should
not run "that" slow.

delete * from qryDateSSNSingleContract
where [owner ssn] in
(select [Owner SSN] from [Data with Import date]
where
[Owner ssn] = qryDateSSNSingleContract.[owner ssn]
and
[Transaction Date] = qryDateSSNCingelContract.[Transaction Date])


If there is a index on both transaction date, and onwer ssn, then the above
query
can run quite fast if you supply the transaction date.

Eg:

strSql = "above sql + "
[Owner ssn] = qryDateSSNSingleContract.[owner ssn]
and
[Transaction Date] = #" & format(date(),"mm/dd/yyy") & "#"

I find the above expression works quite well...I doing it on a table with
(350,000 records). Of course, that is a rather small table, but it only
takes a few seconds to run.

So, if you can "restrict" that sub-query to the possible records based on
the main record..often you can eliminate the table scan...and it will run
fast. make sure you have BOTH the id (ssn) and (date) specified as
conditions in the sub-query...it should run not too bad...

Just make sure both those fields (in each table) used have index on
them.....