UPDATE with JOIN

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.

Advertisement