|
Comments
|
|
Nice
|
|
|
You can you subquery join that is grouped (summed) and update like that, soomething like:
update table1
set field = grouped.total
from table1
inner join (
Select sum(total) as total, idTable from table2 group by idTable) grouped
on table1.idTable = grouped.idTable
I dont see why to use CTE for that, or is it any better. This update is old as SQL is, so with CTE you actually do the same thing.
Or mybe i am wrong ?
|
|
Steve Culshaw on
11/5/2009
Nice pointer to using CTE's
|
|
Rebecca Lewis on
11/5/2009
Really very informative -- and very useful. Thank you!
|
|
Steve Harris on
11/5/2009
would there have been a way to use CTEs to update all the sales columns at once
|
|
|
This is really interesting, but I guess I'm missing the "why." That is, why should I switch to using CTE's instead of temp tables? I'm guessing it's performance, but how significant is that boost?
|
|
|
It would be nice to be able to cut and paste the examples shown in the video. When the video completes, they have partially scrolled off of the screen
|
|
|
Very good info. There is one change I would make in tht code is i was doing this. I would not go to the trouble of creating a temp table. I would just add an into statement in the select of the CTE. This will create the temp table with the very attributes of the fields you are using in the CTE.
James Knapp
Programmer II
Amarillo TX
|
|
|
Nice demonstration on using a CTE. It would be nice to see what the performance differences are between temp tables and CTEs though. I would say that most are more familiar with temp tables and will continue to use those unless there are compelling performance reasons to switch.
|
|
Lloyd Edwards on
11/5/2009
good!
|
|
|
So nice
|
|
|
Good example. However, I am also curios about the performance difference.
|
|
|
Should use the ANSI Standard MERGE and not proprietary UPDATE.. FROM ..
Should use ANSI Standard dates and not dialect
Should use a SUM(CASE..) instead of four separate updates
|
|
David Leach on
11/12/2009
Nice job! Very useful information.
|
|
valluru raghu on
11/13/2009
good to know once again
|
|
|
Just awesome. Good work and thank you.
|
|
|
Good exampel.But this thing can be done by subquery as pointed out by Matija. I just like to know is CTE adds on the performance benefit rather than subquery?
|
|
|
What best does CTE offer compared to temp tables in this scenerio?
|
|
|
MERGE is a new command to SQL Server 2008 and was not available in 2005 which was when the CTE was shipped. Using the MERGE command would only have confused and precluded a large number of the people watching this video from following the code. Whilst I agree that combining the four updates into one query would be much better it would have also detracted from the main concept of the video which was the CTE itself. Perhaps a different scenario should have been used. I too would be interested in knowing the difference in performance between the subquery approach and the CTE. Kudos for filming this and helping out the community Kathi.
|
|
|
Matt Hoenck, Actually there is no performance gain on a CTE as it uses the same query plan a derived table would use. I would rather place the agregation on a temp table and place an Index on the ID so fully optimize my join. I guess I wouldn't use ever a CTE is my data set is greater than 100 rows. the performance gain from Temp Tables are way greater. Try not to use CTE's there is no performance gain think of it as a more readable subquery.
|
|
|
Nice!
|
|
|
Example Script
|
|
Martin Miller on
9/30/2010
Great tip! Thanks Kathi.
|
|
A Alagu Ganesh on
10/14/2010
good
|
|
vin lawrence on
11/9/2010
didn't know they existed thank you
|
|
|
Content was good...but the speaker was speaking very fast without proper explanation of why need to run several updates after CTE's
|