Results 1 to 5 of 5

Thread: can one delete records based on a join?

  1. #1
    Join Date
    Oct 2002
    Posts
    5

    can one delete records based on a join?

    One would think that this would be a fairly common task. If there are any records in Table A whose id is on the "kill list" (Table B), delete that record.

    So, for example:

    DELETE *
    FROM Table_A
    WHERE Table_A.ID = Table_B_List_of_bogus_records.ID;

    I'm attempting this query on Access, so it may not be a SQL problem. Access views "Table_B_List_of_bogus_records.ID" as a parameter needing input.

    Any ideas?

    Much appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Have you tried this

    DELETE Table_A
    FROM Table_A, Table_B_List_of_bogus_records
    WHERE Table_A.ID = Table_B_List_of_bogus_records.ID;

  3. #3
    Join Date
    Oct 2002
    Posts
    5

    still doesn't work...

    skhanal,

    I get a message saying:

    "cannot find file <path to my database>\Table_B_List_of_bogus_records"

    I don't get it...

    But thanks for the suggestion anyway.

    ---Michael

  4. #4
    Join Date
    Oct 2002
    Posts
    5

    it works...

    I've got it to work after all. I tried this query:

    DELETE Table_A.*
    FROM Table_A
    INNER JOIN Table_B_List_of_bogus_records ON Table_A.ID = Table_B_List_of_bogus_records.ID ;

    Seems rather convoluted IMHO, but it works.

    Thanks!

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Oh ya, Access does not recognize the syntax I wrote, it had to be ANSI.

Posting Permissions

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