Delete query using two tables joined - a best way ?

[pbriggs]

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

 

[John Vinson]

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.

Try using a simple join rather than an EXISTS clause:

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


John W. Vinson[MVP]

 

[Guest]

Patrick:

Your first approach was the correct way, but it looks like you designed it
in query design view rather than SQL view and as a result have inadvertently
added the subquery to the outer query's SELECT clause. You can simplify it
as:

DELETE *
FROM LittleRock
WHERE EXISTS
(SELECT *
FROM Organizations
WHERE Organisations.DonorID = LittleRock.DonorID);

You can also use the IN operator:

DELETE *
FROM LittleRock
WHERE DonorID IN
(SELECT DonorID
FROM Organizations);

The EXISTS predicate is the better approach, however. The IN operator is
something of a fossil from the early days of the development of SQL when it
was rooted firmly in relational algebra. Interestingly it was in fact the IN
operation to which the 'Structured' in 'Structured Query Language' originally
referred. As relational calculus came more into play in the mathematical
basis of the language it became redundant and can always be replaced by an
EXISTS operation.

Ken Sheridan
Stafford, England

 

[Guest]

John,

Thank you. That works. I have a long way to go on learning SQL and working
with databases. This should be second nature stuff by now.

Best Regards,

Patrick

 

[Guest]

Ken,

It seems like there are two main ways to do something like this. Either a
Join or using the Exists (in) clause. Thanks for making that very clear and
also for the historical information on Exists and In.

My expertise is becoming theology and political activism more than being a
SQL database analyst. Yet somehow I'm drawn to this work. Hope I can get to
a place where I can combine both.

Cheers,

Patrick

Patrick:

Your first approach was the correct way, but it looks like you designed it
in query design view rather than SQL view and as a result have inadvertently
added the subquery to the outer query's SELECT clause. You can simplify it
as:

DELETE *
FROM LittleRock
WHERE EXISTS
(SELECT *
FROM Organizations
WHERE Organisations.DonorID = LittleRock.DonorID);

You can also use the IN operator:

DELETE *
FROM LittleRock
WHERE DonorID IN
(SELECT DonorID
FROM Organizations);

The EXISTS predicate is the better approach, however. The IN operator is
something of a fossil from the early days of the development of SQL when it
was rooted firmly in relational algebra. Interestingly it was in fact the IN
operation to which the 'Structured' in 'Structured Query Language' originally
referred. As relational calculus came more into play in the mathematical
basis of the language it became redundant and can always be replaced by an
EXISTS operation.

Ken Sheridan
Stafford, England

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

 

[John Vinson]

My expertise is becoming theology and political activism more than being a
SQL database analyst. Yet somehow I'm drawn to this work. Hope I can get to
a place where I can combine both.

<chuckle> I'm attending weekly classes at the "Seminary Without Walls"
preperatory to becoming a certified lay pastor... and I've distributed
quite a few copies of a very simple (need to revise that silly thing
someday!!) church membership database. Hi brother!

John W. Vinson[MVP]

 

[Guest]

John,

If you've got the time, it would be interesting to exchange some thoughts
offline of this discussion thread.

You are much further along in what I'm only now considering. Way to go!

My email is pbriggs (at) greeneggsandham.org (hoping this confuses the email
address harvesters) .

Regards,

Patrick