Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question

    I'm using Access 2002 / 2003. I wroe a simple delete query and I can't get it to run. I keep getting the error "Could not delete from the specified tables."

    I have table1 and table2 and I want to delete from table2 any records that match table1.

    Here is a simple rendering of the SQL: Delete Table2.* from table2 inner join table1 on table1.id = table2.id;

    What am I missing here. I can swear that I've done this hundreds of times. Am I loosing my mind?

    Please help.

    Thanks

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Basic principle looks sound to me.
    I just tested this with a query as below and it was fine

    Code:
    DELETE EmployeeDetails.*
    FROM EmployeeDetailsPlus 
    INNER JOIN EmployeeDetails 
    ON EmployeeDetailsPlus.[Employee ID] = EmployeeDetails.[Employee ID];
    So I guess you need to look at the Link Fields of the Join.
    Also the relationship of the 2 tables
    Finally any other relationships that could be preventing deletions from table 2
    If Table 2 is in a relationship with other data tables, and referential Integrity is functioning, it could be other relationships that
    are preventing the deletions from taking place rather than relationships between table1 and table2.
    Andrew

  3. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Andrew;

    The real life data situation is a bit more complicated and not necessary to go into.
    To see if I was on the right track, I created 2 tables: Table1 and Table2

    Each table has just 1 un-keyed text field called fld1.
    Table1.fld1 values are 2,4,6
    Table2.fld1 values are: 1,2,3,4,5,6,7,8,9,0

    My sql reads like this:
    DELETE Table2.*
    FROM Table2
    INNER JOIN Table1 ON Table2.fld1 = Table1.fld1;

    Shouldn't this work? The fields don't have to be keyed, do they?

    Lance

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by llemli View Post
    Shouldn't this work? The fields don't have to be keyed, do they?
    Yes they need to be keyed, (well at least a Unique Index) to guarantee that each record in one table corresponds with one in the other.

    Another syntax that might work is this. (uses a subquery)


    DELETE Table2.*
    FROM Table2
    where table2.ID in ( Select table1.id from table1)
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks John. Using the sub-query worked perfectly. It deleted multiple occurrences in table2 of every matching record found in Table1. No indexes, no keys.
    Thanks again,
    Lance

Posting Permissions

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