• Update Data in one table with a new or Other table

    by Venkata Koppaka | Dec 14, 2009
    Consider the following scenario - 

    You have data in one table which is old, and then you have data in a newer table which is all good.
    If you have to update the old table with new tables data you can take one of these two approaches

        1. Write a SQL Query that does that
        2. Write a program to iterate and update (ah, I know no one does this except for a purely programming guy who doesn't know any of SQL)

    I am going to focus on writing a SQL Query to update the data - 
    I am going to present you with two ways of writing this query

    Here goes the first one-

    1 update OldTable 
    2 set OldTable.DataToBeUpdated = NewTable.DataToBeUpdated 
    3 from 
    4 OldTable, NewTable 
    5 where 
    6 OldTable.SomeId = NewTable.SomeId 


    And then the second,

    1 update 
    2 ( 
    3 select NewTable.DataToBeUpdated as new_value, 
    4        OldTable.DataToBeUpdated as old_value 
    5 from      
    6        OldTable inner join NewTable  on OldTable.SomeId = NewTable.SomeId 
    7 where 
    8       NewTable.DataToBeUpdated is not Null --Some Criteria 
    9 ) 
    10 set old_value = new_value; 


    Hope this Helps,
    Cheers
    Venkata


    Go comment!