Delete query using two tables joined - a best way ?

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

Guest

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
 
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 )
 
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...
 
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...
 
Back
Top