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
 
J

John Spencer

Is this a question? Are you looking for an alternative?

I think either of the alternatives below might be faster since they are not
using a coordinated sub-query. That may make no difference since the JET
query optimizer could treat them all similarly or your record sets could be
so small that a fast computer hides the difference from us poor, slow
humans.

DELETE DISTINCTROW LittleRock
WHERE LittleRock.Donor_id = Organizations.Donor_ID
FROM LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

OR

DELETE DISTINCTROW LittleRock
FROM LittleRock
WHERE LittleRock.Donorid IN
(SELECT Organizations.DonorID
FROM Organizations )
 
P

Patrick Briggs

John,

Thank you for your answer. With a Join like I had, was it necessary to do
the WHERE clause?

Could I have instead just done this:

DELETE DISTINCTROW
LittleRock
FROM
LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

and is it necessary to put --- LittleRock.* --- instead of LittleRock?

Thanks for the two alternative methods. I am realizing my question was a
very basic one. Something I should have known even as a entry level SQL user.

Regards,

Patrick

John Spencer said:
Is this a question? Are you looking for an alternative?

I think either of the alternatives below might be faster since they are not
using a coordinated sub-query. That may make no difference since the JET
query optimizer could treat them all similarly or your record sets could be
so small that a fast computer hides the difference from us poor, slow
humans.

DELETE DISTINCTROW LittleRock
WHERE LittleRock.Donor_id = Organizations.Donor_ID
FROM LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

OR

DELETE DISTINCTROW LittleRock
FROM LittleRock
WHERE LittleRock.Donorid IN
(SELECT Organizations.DonorID
FROM Organizations )


Patrick Briggs said:
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
Click to expand...
Click to expand...
 
J

John Spencer

Actually, I had a "bad day" and forgot to delete the WHERE Clause and forgot
to specify one (or multiple) fields in the DELETE clause.

The first alternative should have read
DELETE DISTINCTROW LittleRock.*
FROM LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

Although that could read
DELETE DISTINCTROW LittleRock.Donor_ID
FROM ...
Access requires that you list one (or more fields) from the table that you
are deleting records from after the word DELETE. AND no fields from any
other tables.

The second alternative COULD read
DELETE
FROM LittleRock
WHERE LittleRock.Donorid IN
(SELECT Organizations.DonorID
FROM Organizations )

The Access query grid will build this slightly different and will (as far as
I know) always put a table and field reference in the delete clause.
If you save 2nd query above, close it and reopen it in SQL view you will
probably see.

DELETE LittleRock.DonorID
FROM LittleRock
WHERE LittleRock.Donorid IN
(SELECT Organizations.DonorID
FROM Organizations )

Patrick Briggs said:
John,

Thank you for your answer. With a Join like I had, was it necessary to do
the WHERE clause?

Could I have instead just done this:

DELETE DISTINCTROW
LittleRock
FROM
LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

and is it necessary to put --- LittleRock.* --- instead of LittleRock?

Thanks for the two alternative methods. I am realizing my question was a
very basic one. Something I should have known even as a entry level SQL
user.

Regards,

Patrick

John Spencer said:
Is this a question? Are you looking for an alternative?

I think either of the alternatives below might be faster since they are
not
using a coordinated sub-query. That may make no difference since the JET
query optimizer could treat them all similarly or your record sets could
be
so small that a fast computer hides the difference from us poor, slow
humans.

DELETE DISTINCTROW LittleRock
WHERE LittleRock.Donor_id = Organizations.Donor_ID
FROM LittleRock INNER JOIN Organizations
ON LittleRock.Donor_id = Organizations.Donor_ID

OR

DELETE DISTINCTROW LittleRock
FROM LittleRock
WHERE LittleRock.Donorid IN
(SELECT Organizations.DonorID
FROM Organizations )


message
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
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Delete query using two tables joined - a best way ? 6
Delete query using two tables joined - a best way ? 0
delete Using Join Statement 4
Delete Query from Same Table Not Working 0
delete query using inner join 2
DELETE Query wants o know about which table to delete from - JOINE 0
Delete using join statement 4
Self joined table in a delete query 2

Top