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.

Standardizing Object Names - Schema Compare

In a recent build of our schema comparison and synchronization tool xSQL Object we introduced a simple “feature” that is aimed at generating consistent statements and fix name mismatches that occur especially with objects migrated from SQL Server 2000. The idea is that regardless of what the name of the object looks like in the definition of the object in the source database when xSQL Object generates the change script it will use [owner].[object_name]. For example, you may have created a new stored procedure as CREATE PROCEDURE myStoredProcedure … , in the sync script generated by xSQL Object this statement will be CREATE PROCEDURE [owner].[myStoredProcedure].

The “problem” some of the users are running into is that after they execute the synchronization script they expect the databases to be identical however the object definitions are not going to be identical because of those discrepancies in the object name inside the definition of the object (that is only if the names in the source database are not defined as [owner].[objectname]).

While we like the idea of automatically "correcting" the object names (by the way SQL Server Management Studio does the same thing when you script an object) you (the user) may want the synchronization process to reflect exactly what the source database is – in other words you don’t want xSQL Object to standardize the names. In that case you can turn this feature off: find and open xSQL.SQLObjectCompare.UI.exe.config with Notepad and then add the following key under the <appsettings> section: <add key="PerformNameValidation" value="0">

Multiple result sets – SSMS versus xSQL's Script Executor

Up until now we have been promoting Script Executor as a tool that provides for deploying multiple t-sql scripts to many target databases (SQL Server, DB2 and MySQL). What we have neglected to emphasize is a seemingly small feature that many of our users find extremely helpful. If you work with SQL Server it is very likely that you have often executed queries that return multiple result sets and you have experienced firsthand how inconvenient it is to browse through those result sets - see the illustration below and just imagine what that looks like when you have more than just two result sets (click in the image to see full size):

Now, look at the screen shot down below to see how Script Executor displays the results – notice the green arrows (click on the image for full size) that allow you to jump from one result set to the other within the same server. Notice also something even cooler, on the left hand side there is a panel showing the list of databases against which the script was executed – so you can easily see the result sets you got from each database. Furthermore, those databases don’t all have to be SQL Server databases – the same scripts can be executed against multiple SQL Server, DB2 and MySQL databases at the same time.

And last but not least how about this ability to manipulate the result set – right click on the result set and a handy context menu appears allowing you to remove certain columns and or certain rows as well as copying and exporting the result set – see below:

I am of course not advocating ditching SSMS in favor of Script Executor – SSMS is an indispensable tool, but I am simply trying to emphasize the advantages that Script Executor has in certain situations. The reason Script Executor exists is to provide for an easy and efficient deployment of scripts to multiple targets.

Download a copy of Script Executor now and see for yourself how useful this tool can be.

Follow us on Twitter and Like us on Facebook

Follow us on Twitter http://twitter.com/xSQLSoftware and like us on Facebook http://www.facebook.com/xsqlsoftware. In return we will give you a 12 months complimentary upgrade subscription when you purchase our products.