Delete query won't delete

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following delete query that gives me the message "Could not delete
from the specified tables"

The table [Scores] does not have any relationships and I'm able to delete
records manually.


DELETE Scores.*, Scores.ID, Scores.Season, Scores.Visitor, Scores.[Visitor
Score], Scores.Home, Scores.[Home Score], Scores.Margin
FROM Scores INNER JOIN [Scores-Destinct] ON (Scores.Visitor =
[Scores-Destinct].Visitor) AND (Scores.[Visitor Score] =
[Scores-Destinct].[Visitor Score]) AND (Scores.Home = [Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score]) AND (Scores.Margin
= [Scores-Destinct].Margin) AND (Scores.Season = [Scores-Destinct].Season)
WHERE (((Scores.ID)<>[Scores-Destinct].[MinOfID]) AND
((Scores.Season)=[Scores-Destinct].[Season]) AND
((Scores.Visitor)=[Scores-Destinct].[Visitor]) AND ((Scores.[Visitor
Score])=[Scores-Destinct].[Visitor Score]) AND
((Scores.Home)=[Scores-Destinct].[Home]) AND ((Scores.[Home
Score])=[Scores-Destinct].[Home Score]) AND
((Scores.Margin)=[Scores-Destinct].[Margin]));
 
Simplifying your query ( you don't need matching criteria in the where
clause if you have the same matching criteria in the Join clause and you
don't need all the fields in the Delete clause)

Try Adding DistinctRow to the query. If that fails then you might have to
use a subquery.

DELETE DistinctRow Scores.ID
FROM Scores INNER JOIN [Scores-Destinct]
ON
(Scores.Visitor = [Scores-Destinct].Visitor)
AND (Scores.[Visitor Score] = [Scores-Destinct].[Visitor Score])
AND (Scores.Home = [Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score])
AND (Scores.Margin = [Scores-Destinct].Margin)
AND (Scores.Season = [Scores-Destinct].Season)
WHERE
Scores.ID<>[Scores-Destinct].[MinOfID]
 
Thanks - not sure if this is what exactly you where instructing me to do but
this is what work.

I deleted all the WHERE statements except for [MinOfID]

Added a new query statement

DELETE DISTINCTROW Scores.*, Scores.ID
FROM Scores INNER JOIN [Scores-Destinct] ON (Scores.Season =
[Scores-Destinct].Season) AND (Scores.Margin = [Scores-Destinct].Margin) AND
(Scores.[Home Score] = [Scores-Destinct].[Home Score]) AND (Scores.Home =
[Scores-Destinct].Home) AND (Scores.[Visitor Score] =
[Scores-Destinct].[Visitor Score]) AND (Scores.Visitor =
[Scores-Destinct].Visitor)
WHERE (((Scores.ID)<>[Scores-Destinct].[MinOfID]));

It work but I not sure why it did verses just the one!?




--
David McKnight


John Spencer said:
Simplifying your query ( you don't need matching criteria in the where
clause if you have the same matching criteria in the Join clause and you
don't need all the fields in the Delete clause)

Try Adding DistinctRow to the query. If that fails then you might have to
use a subquery.

DELETE DistinctRow Scores.ID
FROM Scores INNER JOIN [Scores-Destinct]
ON
(Scores.Visitor = [Scores-Destinct].Visitor)
AND (Scores.[Visitor Score] = [Scores-Destinct].[Visitor Score])
AND (Scores.Home = [Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score])
AND (Scores.Margin = [Scores-Destinct].Margin)
AND (Scores.Season = [Scores-Destinct].Season)
WHERE
Scores.ID<>[Scores-Destinct].[MinOfID]

David McKnight said:
I have the following delete query that gives me the message "Could not
delete
from the specified tables"

The table [Scores] does not have any relationships and I'm able to delete
records manually.


DELETE Scores.*, Scores.ID, Scores.Season, Scores.Visitor, Scores.[Visitor
Score], Scores.Home, Scores.[Home Score], Scores.Margin
FROM Scores INNER JOIN [Scores-Destinct] ON (Scores.Visitor =
[Scores-Destinct].Visitor) AND (Scores.[Visitor Score] =
[Scores-Destinct].[Visitor Score]) AND (Scores.Home =
[Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score]) AND
(Scores.Margin
= [Scores-Destinct].Margin) AND (Scores.Season = [Scores-Destinct].Season)
WHERE (((Scores.ID)<>[Scores-Destinct].[MinOfID]) AND
((Scores.Season)=[Scores-Destinct].[Season]) AND
((Scores.Visitor)=[Scores-Destinct].[Visitor]) AND ((Scores.[Visitor
Score])=[Scores-Destinct].[Visitor Score]) AND
((Scores.Home)=[Scores-Destinct].[Home]) AND ((Scores.[Home
Score])=[Scores-Destinct].[Home Score]) AND
((Scores.Margin)=[Scores-Destinct].[Margin]));
Click to expand...
Click to expand...
 
The major difference was the addition of DistinctRow. All the rest was just
simplifying your query statement.

In ACCESS SQL you really only need one reference to a field of the table to
be deleted in the Delete Clause.

In SQL, if you have an equi-join between two fields (x=y) then you don't
need to repeat that same x=y in the where clause. It is redundant. It may
actually make the query slower or it may not have any impact at all. The
impact depends on how smart the interpreter is in actually constructing the
query from your query statement. In any case, it makes the SQL statement
simpler for the human to read.


David McKnight said:
Thanks - not sure if this is what exactly you where instructing me to do
but
this is what work.

I deleted all the WHERE statements except for [MinOfID]

Added a new query statement

DELETE DISTINCTROW Scores.*, Scores.ID
FROM Scores INNER JOIN [Scores-Destinct] ON (Scores.Season =
[Scores-Destinct].Season) AND (Scores.Margin = [Scores-Destinct].Margin)
AND
(Scores.[Home Score] = [Scores-Destinct].[Home Score]) AND (Scores.Home =
[Scores-Destinct].Home) AND (Scores.[Visitor Score] =
[Scores-Destinct].[Visitor Score]) AND (Scores.Visitor =
[Scores-Destinct].Visitor)
WHERE (((Scores.ID)<>[Scores-Destinct].[MinOfID]));

It work but I not sure why it did verses just the one!?




--
David McKnight


John Spencer said:
Simplifying your query ( you don't need matching criteria in the where
clause if you have the same matching criteria in the Join clause and you
don't need all the fields in the Delete clause)

Try Adding DistinctRow to the query. If that fails then you might have
to
use a subquery.

DELETE DistinctRow Scores.ID
FROM Scores INNER JOIN [Scores-Destinct]
ON
(Scores.Visitor = [Scores-Destinct].Visitor)
AND (Scores.[Visitor Score] = [Scores-Destinct].[Visitor Score])
AND (Scores.Home = [Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score])
AND (Scores.Margin = [Scores-Destinct].Margin)
AND (Scores.Season = [Scores-Destinct].Season)
WHERE
Scores.ID<>[Scores-Destinct].[MinOfID]

message
I have the following delete query that gives me the message "Could not
delete
from the specified tables"

The table [Scores] does not have any relationships and I'm able to
delete
records manually.


DELETE Scores.*, Scores.ID, Scores.Season, Scores.Visitor,
Scores.[Visitor
Score], Scores.Home, Scores.[Home Score], Scores.Margin
FROM Scores INNER JOIN [Scores-Destinct] ON (Scores.Visitor =
[Scores-Destinct].Visitor) AND (Scores.[Visitor Score] =
[Scores-Destinct].[Visitor Score]) AND (Scores.Home =
[Scores-Destinct].Home)
AND (Scores.[Home Score] = [Scores-Destinct].[Home Score]) AND
(Scores.Margin
= [Scores-Destinct].Margin) AND (Scores.Season =
[Scores-Destinct].Season)
WHERE (((Scores.ID)<>[Scores-Destinct].[MinOfID]) AND
((Scores.Season)=[Scores-Destinct].[Season]) AND
((Scores.Visitor)=[Scores-Destinct].[Visitor]) AND ((Scores.[Visitor
Score])=[Scores-Destinct].[Visitor Score]) AND
((Scores.Home)=[Scores-Destinct].[Home]) AND ((Scores.[Home
Score])=[Scores-Destinct].[Home Score]) AND
((Scores.Margin)=[Scores-Destinct].[Margin]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top