Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Delete query using two tables joined - a best way ?

Discussion in 'Information Technology' started by pbriggs@greeneggsandham.org, Jul 28, 2006.

  1. pbriggs@greeneggsandham.org

    pbriggs@greeneggsandham.org
    Expand Collapse
    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
     
  2. Loading...


  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 21 Jul 2006 14:50:30 -0700, pbriggs@greeneggsandham.org wrote:

    >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]
     
  4. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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

    "pbriggs@greeneggsandham.org" wrote:

    > 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
    >
    >
     
  5. Patrick Briggs

    Patrick Briggs
    Expand Collapse
    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

    "John Vinson" wrote:

    > On 21 Jul 2006 14:50:30 -0700, pbriggs@greeneggsandham.org wrote:
    >
    > >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]
    >
    >
    >
    >
     
  6. Patrick Briggs

    Patrick Briggs
    Expand Collapse
    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

    "Ken Sheridan" wrote:

    > 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
    >
    > "pbriggs@greeneggsandham.org" wrote:
    >
    > > 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
    > >
    > >

    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 25 Jul 2006 08:57:01 -0700, Patrick Briggs
    <PatrickBriggs@discussions.microsoft.com> wrote:

    >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]
     
  8. Patrick Briggs

    Patrick Briggs
    Expand Collapse
    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

    "John Vinson" wrote:

    > On Tue, 25 Jul 2006 08:57:01 -0700, Patrick Briggs
    > <PatrickBriggs@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     

Share This Page