Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    7

    Question Unanswered: Remove non existing clients in table 1 based on table 2

    Hi,

    what I need to do is:

    1- Table 1 is old and contains some no longer existing clients name

    2- Table 2 is new and do not contain non existing clients names


    How do I design the query in a way the code look in table 2 and remove from table 1 the no longer existing clients, please ?

    Thanks

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    To make sure I follow you...

    You want to Delete ALL the records from Table1, that refer to Clients that do not exist in Table2?
    Looking for the perfect beer...

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The general structure of such a query is like this:
    Code:
    DELETE Table1.*
    FROM Table1
    WHERE Table1.Field1 In (
        SELECT Table1.Field1
        FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
        WHERE Table2.Field1 Is Null
    );
    For some strange reason specific to Access you have to specify Table1.* in this case, while the standard SQL syntax would be DELETE FROM Table1. If you omit Table1.* (or Table1.<Something> for that matter) an error message pops up saying that the query must have at least one destination field.
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    7
    Quote Originally Posted by kez1304 View Post
    To make sure I follow you...

    You want to Delete ALL the records from Table1, that refer to Clients that do not exist in Table2?
    Yes!
    If client is Table 1 exist, and, is no longer a client with us he will not show in Table 2!

    Then, he must be removed from Table 1.

    Sincerly

  5. #5
    Join Date
    Aug 2011
    Posts
    7

    Smile

    Quote Originally Posted by Sinndho View Post
    The general structure of such a query is like this:
    Code:
    DELETE Table1.*
    FROM Table1
    WHERE Table1.Field1 In (
        SELECT Table1.Field1
        FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
        WHERE Table2.Field1 Is Null
    );
    For some strange reason specific to Access you have to specify Table1.* in this case, while the standard SQL syntax would be DELETE FROM Table1. If you omit Table1.* (or Table1.<Something> for that matter) an error message pops up saying that the query must have at least one destination field.

    Thank you !!! It's working !

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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