locked
How do I update a table with new data? RRS feed

  • Question

  • Hi,

    I have a table called "ORIGINAL" with four columns: A, B, C, and D; columns A and B are the keys. I have another table called "NEW DATA" that has columns A and B, as well as two new columns, E and F.

    I need to merge (my term as I am still a SQL novice) the two tables together so that table "ORIGINAL" contains the new columns "E" and "F" in addition to columns "A", "B", "C" and "D". I will be joining them on columns "A" and "B".

    How do I write my query?

    Thanks.
    Dave
    Friday, December 4, 2009 5:54 PM

Answers

  • You have two options,

    1. Either create two new columns in ORIGINAL table called E and F, and then move the data from NEW DATA to ORIGINAL as follows

    ALTER TABLE ORIGINAL
    ADD E DATATYPE
    ALTER TABLE ORIGINAL
    ADD F DATATYPE
    
    UPDATE O SET E = N.E
    ,F = N.F 
    FROM ORIGINAL O
    JOIN NEWDATA N ON O.A = N.A AND O.B = N.B

    2. Move everything from these two tables into a new table as follows

    SELECT O.A,O.B,O.C,O.D,N.E,N.F
    INTO NewOriginalTable
    FROM ORIGINAL O
    JOIN NEWDATA N ON O.A = N.A AND O.B = N.B

    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by brewerdi Friday, December 4, 2009 6:16 PM
    Friday, December 4, 2009 6:01 PM

All replies

  • SELECT O.A, O.B, O.C, O.D, N.E, N.F
      FROM Original O
      INNER JOIN New N
         on O.A = N.A
        and O.B = N.B


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by brewerdi Friday, December 4, 2009 6:12 PM
    • Unmarked as answer by brewerdi Friday, December 4, 2009 6:16 PM
    Friday, December 4, 2009 5:56 PM
  • You have two options,

    1. Either create two new columns in ORIGINAL table called E and F, and then move the data from NEW DATA to ORIGINAL as follows

    ALTER TABLE ORIGINAL
    ADD E DATATYPE
    ALTER TABLE ORIGINAL
    ADD F DATATYPE
    
    UPDATE O SET E = N.E
    ,F = N.F 
    FROM ORIGINAL O
    JOIN NEWDATA N ON O.A = N.A AND O.B = N.B

    2. Move everything from these two tables into a new table as follows

    SELECT O.A,O.B,O.C,O.D,N.E,N.F
    INTO NewOriginalTable
    FROM ORIGINAL O
    JOIN NEWDATA N ON O.A = N.A AND O.B = N.B

    Abdallah, PMP, ITIL, MCTS
    • Marked as answer by brewerdi Friday, December 4, 2009 6:16 PM
    Friday, December 4, 2009 6:01 PM
  • Thanks Phil for your quick response!


    So I don't have to ALTER the Original table first to add the new column as blank and then run the query? Correct? I only need to run your query?


    Thanks again for your prompt reply.
    Dave
    Friday, December 4, 2009 6:02 PM
  • Yep, there are many ways, as Abdallah indicated.  A view is another way, where you aren't physically combining the data, but instead present the data as if it were a single table.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, December 4, 2009 6:03 PM
  • Phil,

    I only want to update my original table with the new columns; I don't want to create a view. So I assume from your original answer your query will do the job. Thanks.

    Abdallah,

    On your first query, I don't understand the ",F". Is this a typo? Again, I am very new to SQL, so be easy on me :=)
    UPDATE O SET E = N.E
    ,F = N.F

    Thanks for your help as well.
    Dave
    Friday, December 4, 2009 6:13 PM
  • Dave,
    My query does nothing to the original table - it just selects data in a fashion that assembles the data together, but it isn't persisted.

    Abdallah's example walks through adding two new columns to the OriginalTable.  The new columns are called E & F.

    Then he updates the values in the columns E & F.  That's why you see E and F in the UPDATE statement.





    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Edited by Phil Brammer Friday, December 4, 2009 6:20 PM Removing edits again.
    Friday, December 4, 2009 6:14 PM
  • It was not a typo. In UPDATE command you can update multiple columns (you list them separated by commas), like in this sample:

    UPDATE O set myColumn =  N.myColumn, myOtherColumn = N.myOtherColumn

    FROM myOldTable O INNER JOIN myNewTable N on O.ID = N.ID
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, December 4, 2009 6:16 PM
  • Phil,

    I only want to update my original table with the new columns; I don't want to create a view. So I assume from your original answer your query will do the job. Thanks.

    Abdallah,

    On your first query, I don't understand the ",F". Is this a typo? Again, I am very new to SQL, so be easy on me :=)
    UPDATE O SET E = N.E
    ,F = N.F

    Thanks for your help as well.
    Dave

    After I created two new columns called E and F using the ALTER statement, I updated them with the data from NEWDATA table baed on the JOIN between A and B from both tables.
    Abdallah, PMP, ITIL, MCTS
    Friday, December 4, 2009 6:18 PM
  • Phil,

    Oh, I didn't realize that. I needed to update my original table to include those new columns. Based on that, I guess I have to mark Abdallah's post as the answer.

    Thanks for the clarification...this novice needs all the help he can get =:)

    Dave
    Friday, December 4, 2009 6:18 PM