UPDATE with JOIN

Posted: January 30, 2012 in SQL Server
Tags: , , , , , ,

If you need to update data of a table (DestinationTable) with data of another table (SourceTable) there are 3 ways you can get this done in SQL.

Method 1

This is the most common and simple way with using a sub query.

  1. UPDATE DestinationTable SET DestinationColumn =
  2.     (SELECT ValueColumn FROM SourceTable WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn)

 

Method 2

This users the most common FROM clause to join the two tables as shown below.

  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM SourceTable
  3. WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Method 3

Last method mentioned below uses the join clause to join both the tables to make the update happen properly.

  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM DestinationTable
  3. INNER JOIN SourceTable ON DestinationTable.MappingColumn = SourceTable.MappingColumn

 

Hope these helps.

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