Delete query using two tables joined - a best way ?

P

Patrick Briggs

Trying to do a delete query using two tables joined has been a little harder
than I expected. I had a list of donor ids in a table called Organizations.
I had a main table with all the gift records namedn LittleRock. They were
linked on donor_id.

I wanted to delete all records from the LittleRock table where donor_id =
donor_id from Organizations.

Before I go further here is:

What did work:

DELETE DISTINCTROW LittleRock.*, Exists (SELECT * FROM Organizations WHERE
LittleRock.Donor_id = Organizations.Donor_ID)
FROM LittleRock
WHERE (((Exists (SELECT * FROM Organizations WHERE ))<>False));

http://tinyurl.com/hkx79

I don't understand why it works and why both of these SQL queries didn't:

What didn’t work:

DELETE LittleRock.*
FROM Organizations INNER JOIN LittleRock ON Organizations.Donor_id =
LittleRock.Donor_Id;

And this didn’t either:

DELETE DISTINCTROW LittleRock.Donor_Id, LittleRock.[Folder Name],
LittleRock.fn, LittleRock.ln, LittleRock.add1, LittleRock.add2,
LittleRock.City, LittleRock.State, LittleRock.Zip, LittleRock.Gift_Date,
LittleRock.Gift_Amount, LittleRock.Gift_type, LittleRock.Key_Code,
LittleRock.Key_Desc, LittleRock.First_Gift_Date
FROM Organizations INNER JOIN LittleRock ON Organizations.Donor_id =
LittleRock.Donor_Id
WHERE (([LittleRock]![Donor_ID]=[Organizations]![Donor_ID]));

Can anybody explain what is going on here. Why one way works but the other
two don't?

Thanks,

Patrick Briggs
 

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