Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unanswered: UPdating a table

    Ok this may be really simple but I am totally not seeing this at the moment. I have a table and I need to update this table with records from another table but I need all the rows from both tables and those that match.

    Table A
    ID Data1
    A 100
    B 200


    Table B
    ID Data2
    B 333
    C 345


    Results should be
    ID Data1 Data2
    A 100
    B 200 333
    C 345

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You want a LEFT JOIN.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    35
    I think I want a Full Join but thanks. Can anyone verify?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Nope, Pat is right, it's LEFT JOIN that you need. Start with something like this:

    select a.ID, a.Data1, b.Data2
    from TableA a
    left outer join TableB b
    on a.ID = b.ID
    union
    select b.ID, b.Data2, a.Data1
    from TableA a
    left outer join TableB b
    on b.ID = a.ID

  5. #5
    Join Date
    Jan 2004
    Posts
    35
    Hmm I hate to play devil's advocate but if I were to do a union query wouldn't I get duplicate rows? Using my example and doing a union with a left outer join, wouldn't my results be?



    A 100
    B 200 333
    B 200 333
    C 345



    If not can you explain?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want a full outer join with a coalesce of the ID values:

    select coalesce(TableA.ID, TableB.ID) as ID,
    TableA.Data1,
    TableB.Data2
    from TableA
    full outer join TableB
    on TableA.ID = TableB.ID

    This will give you the results you requested, except for the fact that in your example you have value 345 as column Data2 in table B, but in the result set you show it in the Data1 column. Not sure if you really intended that.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but what are you updating?
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2004
    Posts
    35
    Yes that is what I meant and that is what I needed to do. Basically I have 2 unrelated recordsets that link to one key field but I need all the records from both the recordsets. IF that makes sense. I think I need the full outer join. Thanks again.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then you are going to continue to have problems writing code for these tables because your schema is not normalized. Data1 and Data2 are either identical fields (in which case they should be stored as separate records rather than separate columns) or they are different fields (in which case Data2 values should not be stored in the same column as Data1 values).
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Lindman does schemas...

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You sure have picked an odd way to boost your post count, Bob.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jan 2004
    Posts
    35
    Sorry, I know I am not explaining this well. IT isn't that my tables aren't normalized, however even if they weren't I dont have any control over it. IT is just what is being measured that is difficult.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Originally posted by blindman
    You sure have picked an odd way to boost your post count, Bob.
    Hey, I learned from the best!

Posting Permissions

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