Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Unanswered: Insert From query

    Hi guys I need to insert some updated Terms from another table but it keeps telling me that it cant because it would be creating duplicates. The funny thing about it is I did a comparison with the two tables one table is called UpdateForTerms Table and the other is called TERMINATION

    Code:
    SELECT UpdateForTerms.EMPLOYEE, UpdateForTerms.EMP_STATUS, UpdateForTerms.TERM_DATE, UpdateForTerms.DEPARTMENT, UpdateForTerms.LAST_NAME, UpdateForTerms.FIRST_NAME, UpdateForTerms.DESCRIPTION
    FROM UpdateForTerms LEFT JOIN TERMINATION ON UpdateForTerms.EMPLOYEE = TERMINATION.[TM #]
    WHERE (((TERMINATION.[TM #]) Is Null));
    I made the results of this into a query so I could update the Termination table. The reason I created this query is so that I could weed out those records the Terminations table already had. Kind of a futile attempt it seems
    Last edited by desireemm; 12-03-04 at 17:17.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    use code tags with brackest

    [ code ]

    [ /code ]

    with no spaces
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    OOps

    Sorry about that Brett

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your query:
    Code:
    SELECT
       UpdateForTerms.EMPLOYEE, UpdateForTerms.EMP_STATUS
    ,  UpdateForTerms.TERM_DATE, UpdateForTerms.DEPARTMENT
    ,  UpdateForTerms.LAST_NAME, UpdateForTerms.FIRST_NAME
    ,  UpdateForTerms.DESCRIPTION
       FROM UpdateForTerms
       LEFT JOIN TERMINATION ON UpdateForTerms.EMPLOYEE = TERMINATION.[TM #]
       WHERE (((TERMINATION.[TM #]) Is Null));
    should return the rows from the UpdateForTerms table that are NOT in the TERMINATION table. Isn't that what you wanted?

    I'd be willing to bet that you had two or more rows in the UpdateForTerms table with the same EMPLOYEE value, so that when you tried to insert the second one of the rows from the UpdateForTerms table into the TERMINATION table, that is what caused a key problem.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps a well place DISTINCT clause (somewhere near the front, for instance) would benefit this INSERT query...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Perhaps a well place DISTINCT clause (somewhere near the front, for instance) would benefit this INSERT query...
    As long as the rows are complete duplicates (or at least for the columns being selected) that is true. As Desireemm works in the gaming industry (I know, that wasn't anywhere in her post, but it has been in previous threads) it is quite likely that the rows may have different values.

    If they are complete duplicates, then a DISTINCT is a great solution. If there are rows with the same [TM #] but different information in other columns, then she'll need to write criteria or possibly even code to determine which UpdateForTerms row is applicable.

    -PatP

Posting Permissions

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