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 table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--Retrieving data
SELECT * FROM Tmp

--Update Using another table
UPDATE Tmp
    SET Tmp.JobTitle = HE.JobTitle + ', updated'
    FROM [HumanResources].[Employee] AS HE
    WHERE Tmp.BusinessEntityID = HE.BusinessEntityID


--Retrieving data
SELECT * FROM Tmp
SELECT * FROM HumanResources.Employee
GO

--Dropping Table
DROP TABLE Tmp
GO

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 table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--Retrieving data
SELECT * FROM Tmp

--Update Using another table
UPDATE Tmp
    SET Tmp.JobTitle = HE.JobTitle + ', updated'
    FROM [HumanResources].[Employee] AS HE
    --WHERE Tmp.BusinessEntityID = HE.BusinessEntityID


--Retrieving data
SELECT * FROM Tmp
SELECT * FROM HumanResources.Employee
GO

--Dropping Table
DROP TABLE Tmp
GO

Also you can use complex update statement using conditions.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--Retrieving data
SELECT * FROM Tmp

--Update Using another table
UPDATE 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 data
SELECT * FROM Tmp
SELECT * FROM HumanResources.Employee
GO

--Dropping Table
DROP TABLE Tmp
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s