One to many join – pick only one corresponding row from the "many" table

It is hard to "synthesize" this problem into one sentence hence the cryptic title, but here is the long description: You have table T1 that has a one to many relationship with table T2. You need to update a particular column, let's say C1, on table T1 with a value that can be extracted (with some manipulation) from one of the corresponding rows on table T2.

The first thing that comes to mind is a simple update like this:
    UPDATE T1 SET C1 = SomeFunction(t2.C1)
          FROM T1 INNER JOIN T2 ON t1.ID = t2.t1ID
          WHERE t2.c1 fulfills some criteria – we need this since not all the rows on T2 contain the values we need.

The problem with this is that it keeps needlessly updating T1 rows as many times as it finds corresponding rows on T2. For each row on T1 we need to pick only one of the corresponding rows from T2 that fulfills the criteria. One option would be to use a cursor, but we want to avoid using a cursor and handle this within one query. Here is one of the ways of accomplishing this:
   UPDATE T1 SET C1 = tempt2.C1
          FROM T1 INNER JOIN
                          (SELECT DISTINCT t1ID, SomeFunction(t2.C1) AS C1
                                FROM T2 WHERE t2.c1 fulfills some criteria
                          ) AS tempt2  ON t1.ID = tempt2.t1ID

Now, the sub-query will produce a temporary table that contains a single corresponding row for each row on T1 and it also contains the value with which we need to update column C1 on T1. This is based on the assumption that SomeFunction(t2.C1) generates the same value for all rows in T2 that are related to the same row on T1.

With small tweaks in the sub-query you can use this solution for all scenarios when you need to restrict the number of rows that are returned from a one to many JOIN.

Note: don’t forget to try our cool SQL Tools and remember that when you follow us on Twitter http://twitter.com/xsqlsoftware and “like” us on Facebook http://www.facebook.com/xsqlsoftware you will get a complimentary 12 months upgrade subscription when you purchase any of our products.