Updating from other Tables

If you want to synchronize data from another table only for once, you can use this way. When you are going to update from another table don’t forget to give a condition.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] GO
--Retrieving dataSELECT * FROM Tmp
--Update Using another tableUPDATE Tmp
    SET Tmp.JobTitle = HE.JobTitle + ', updated'
    FROM [HumanResources].[Employee] AS HE
    WHERE Tmp.BusinessEntityID = HE.BusinessEntityID

--Retrieving dataSELECT * FROM TmpSELECT * FROM HumanResources.EmployeeGO
--Dropping TableDROP TABLE TmpGO

If you don’t specify a condition for update it will update all the rows with one value. For example, if you run below code you will see the difference.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] GO
--Retrieving dataSELECT * FROM Tmp
--Update Using another tableUPDATE Tmp
    SET Tmp.JobTitle = HE.JobTitle + ', updated'
    FROM [HumanResources].[Employee] AS HE
    --WHERE Tmp.BusinessEntityID = HE.BusinessEntityID


--Retrieving dataSELECT * FROM TmpSELECT * FROM HumanResources.EmployeeGO
--Dropping TableDROP TABLE TmpGO

Also, you can use complex update statement using conditions.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] GO
--Retrieving dataSELECT * FROM Tmp
--Update Using another tableUPDATE Tmp 
    SET Tmp.JobTitle = HE.JobTitle + ', updated'
    FROM [HumanResources].[Employee] AS HE, Tmp AS T
    WHERE T.BusinessEntityID = HE.BusinessEntityID
    AND HE.Gender = 'M'

--Retrieving dataSELECT * FROM TmpSELECT * FROM HumanResources.EmployeeGO
--Dropping TableDROP TABLE TmpGO