Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Doylestown, Pennsylvania, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL help (Access 97)

    I'm trying to get access to delete rows out of a table based upon the results of a subquery. I get an error based upon the addition of the IN statement. It keeps insisting that the parentheses are wrong. I've added and subtracted more parens than I can count and still get the error.

    Here's my SQL code
    Delete from Subscribers where email in (select m.ID from members m, subscribers s where m.email = s.email);

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL help (Access 97)

    I don't think there is anything wrong with the parentheses, but the SQL should probably read

    DELETE FROM Subscribers WHERE ID In (select m.ID from members m, subscribers s where m.email = s.email)

    because in your version, you're trying to compare Subscribers.email to members.ID.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Location
    Doylestown, Pennsylvania, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL help (Access 97)

    You're right. And it does in the original, and that's why I'm confused by the message I get.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL help (Access 97)

    Your code :
    Delete from Subscribers where email in (select m.ID from members m, subscribers s where m.email = s.email);
    probably needs the * to work as well as changing the field m.email to m.ID as Hans has suggested. Eg.
    Delete * from Subscribers where ID in (select m.ID from members m, subscribers s where m.email = s.email);

    I'm presuming that ID is in both tables.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •