Use of Self Join in Update Query

Posted: May 3, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Some times one might need to update a record in a table based on a calculation on some field of the same table.It can not be done without using Self Join.
e.g. Consider the following data, there is a region named Region1 and this region includes C1 ,C2 and C3 cities.
So the population of Region1 is equal to the sum of population of these three cities.Now there are two more cities C4 and C5 that needs to be added in Region1.
Now population of Region1 should be updated by adding the population of C4 and C5 into it.

Geography	Population
C1	         136376
C2	         154644
C3	         176040
C4	         148793
C5	         138288
C6	         137754
C7	         141093
Region1	         467060
Region2	         278847

Following query can be used to achieve the same.

UPDATE [Table] as T1,[Table] as T2,[Table] as T3
           Set T1.Population = T1.Population + T2.Population+T3.Population
          T1.[Geography] = 'Region1'
                   AND T2.[Geography] = 'C4'
                            AND T3.[Geography] ='C5'

Share your thoughts/feedback

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s