Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    Mesa, AZ
    Posts
    3

    Unanswered: Deleting like records

    I have a user that has 2 databases. #1 has apx. 20,000 records. #2 has apx. 1000 records that are identical to records in #1.
    What he wants to do is to delete all records in #1 that match records in #2.
    All records in both tables are unique so matching just one or two fields should suffice.
    Can this be done using a delete query? and if so how do I implement this query. Caution, I am really a greenhorn here!
    Any help would be appreciated.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Create a new query in design view. Add Table1 and Table2. Join the related fields between the 2 files. Double-click the * in Table1 (the table you are deleting from). It should now appear in your grid. Change your Query Type to a Delete Query. You can do this by clicking on the Query Type button in the toolbar. You should now have a Row titled "Delete" in your grid with a value of 'From' for the first column. You can click the 1st button in the Toolbar to see what you are going to delete. Return to Design View and click the button with the ! in order to run the query.

    TD

  3. #3
    Join Date
    Nov 2004
    Location
    Mesa, AZ
    Posts
    3

    Angry

    Quote Originally Posted by buckeye_td
    Create a new query in design view. Add Table1 and Table2. Join the related fields between the 2 files. Double-click the * in Table1 (the table you are deleting from). It should now appear in your grid. Change your Query Type to a Delete Query. You can do this by clicking on the Query Type button in the toolbar. You should now have a Row titled "Delete" in your grid with a value of 'From' for the first column. You can click the 1st button in the Toolbar to see what you are going to delete. Return to Design View and click the button with the ! in order to run the query.

    TD
    This produces a SQL line very similar to what I had and with the same result:

    DELETE Table1.*
    FROM Table1 INNER JOIN Table2 ON (Table1.last = Table2.Last) AND (Table1.first = Table2.First);

    However upon trying to execute I get an error:
    "Could not delete from specified tables"

    Anyone have any suggestions as to what I am doing wrong?????

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Go into the query design view and click on Properties and change the Unique Records value to Yes.

    TD

  5. #5
    Join Date
    Nov 2004
    Location
    Mesa, AZ
    Posts
    3

    Thumbs up

    Thank you VERY much

Posting Permissions

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