Delete query with joined tables


Joined
Feb 10, 2010
Messages
4
Reaction score
0
I have a database in which I import data from different files. One of these files contains data from different sources. The different sources may contain data from different dates. I.e. In file A source 1 can have data from 24/01/2013 whereas source 2 can have data from 25/01/2013.

When importing data I firast store the data in a temporary table before, then adding any new sources to a separate table before joining these tables sending the source ID to the holdings table rather than the source name in the temporary file.

Now I have tried to create a delete query to delete any holdings in which the sourceID match the source in the temporary file AND the dta is the same AND the load ID is the same.

This query will select the relevant rows from tblHoldings howevere will not delete them as "access can not delete from the specified table":

DELETE tblHoldings.*
FROM (SELECT DISTINCT tblRawGroupTreasury.inDate, tblSources.SourceID, "GT" AS LoadID
FROM tblRawGroupTreasury
INNER JOIN tblSources ON tblRawGroupTreasury.Source = tblSources.SourceName
) AS Step1
INNER JOIN tblHoldings ON (Step1.inDate = tblHoldings.HoldingDate)
AND (Step1.SourceID = tblHoldings.SourceID)
AND (Step1.LoadID = tblHoldings.LoadID);

I have tried to amend this so that it only references one table in the FROM part, but that query seems to get stuck in a loop as it never finishes:

DELETE *
FROM tblHoldings
WHERE EXISTS(SELECT *
FROM (SELECT DISTINCT tblRawGroupTreasury.inDate, tblSources.SourceID, "GT" AS LoadID
FROM tblRawGroupTreasury
INNER JOIN tblSources ON tblRawGroupTreasury.Source = tblSources.SourceName
) AS Step1
WHERE ((Step1.inDate = tblHoldings.HoldingDate)
AND (Step1.SourceID = tblHoldings.SourceID)
AND (Step1.LoadID = tblHoldings.LoadID))
)=TRUE;


Anybody out there who might help me?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top