Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Check your TSQL update statements

Posted by thomasivarssonmalmo on May 4, 2008

This is an important blog post about the limitations in TSQL update when you join with other tables.
 
Seems like I have used this TSQL "enhancement" myself without knowing the limitations.
 
The blog post points to the new merge statement in SQL Server 2008 and some other solution.
 
There is another non-ANSI standard solution with update CTE(Common table expressions) that is mentioned in the TSQL Querying book that I have linked to on my blog.
 
I have a simple example here where I update a key in the first table with a key from the second table.
 
WITH UPDATE_CTE AS
(Select t1.MyDimKey as SetMyDimKey, t2.MyDimKey as GetMyDimKey
From Table1 as t1 join Table2 as t2 On
t1.MyDimKey = t2.MyDimKey)
UPDATE_CTE
Set SetMyDimKey = GetMyDimKey
 
Why did I not read about this earlier?
 
What have I been doing during the last month?
 
I am currently using most of my professional time and some hours after work to learn more about Performance Point Monitoring and Analytics. The time for writing here have been limited during the last month. Still this geeky blog had 15 thousand hits since I started 12 months ago.
 
I cannot compete with Vidas or Chris Webb since I only write about what come across my mind in the MS-BI area. These guys covers everything about SSAS and MDX with links, blog posts and personal comments. 
 
It is great that my blog posts on attribute relations have increased the number of visits during the last months.
 
A new area of interest of mine is the data mining components for Excel 2007 which requires you to learn more about DM. Even if these components give you an eay way to analyze data and see new patterns you will always get a questions about the result and theory behind it.
 
Thanks to Vidas, Chris Webb, Darren Gosbell and Mosha for posting comments here. And thanks to all others as well.
Advertisements

2 Responses to “Check your TSQL update statements”

  1. Vidas said

    Thomas,
     
    It is always a pleasure to read your blog. 15 thousand hits – that is a lot in Analysis Services and MDX area!
     
    Vidas
     
     

  2. Thomas said

    Vidas, some of them are RSS-feeds but they are hits anyway. Thanks for your nice comment. You are doing a great job with your SSAS-site.
     
    Thomas

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

 
%d bloggers like this: