|
Comments:
|
|
It looks good, but I think the UPDATE .. FROM .. WHERE is simpler and more logical than CTE.
|
|
Plamen Ratchev on 11/12/2008
In my opinion the use of the CTE makes the code more readable and more convenient to troubleshoot and maintain. It is very easy to test the CTE with a SELECT statement to check what values will get updated. Plus you get the same efficient plan as you would get with the UPDATE with JOIN syntax. Also, there are some cases where incorrectly written UPDATE with FROM clause that does not reference the target table for the update may produce very unpleasant results (see more details in the following article by Jeff Moden: http://www.sqlservercentral.com/articles/Performance+Tuning/62278/).
|
|
|
talks too fast and does't explain what he is doing
|
|
|
Good example.
|
|
|
I would have to slightly disagree with your statement:
"created once then used multiple times in a query or script".
I would only say that it can be used multiple times in a query. It would not qualify for multiple times in a script because it would need to be redefined within the execution scope of each SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement that utilizes it. You cannot just define it once in a script and use it throughout multiple/separate queries within the script.
I do find it an interesting alternative to the "UPDATE .. FROM .. WHERE" syntax though.
|
|
|
Adam, you are correct. I am not sure why that description was added to the video, I do not discuss the nature of CTEs in the video at all. The CTE is valid only in the scope of the consuming statements as you pointed out (including the MERGE statement in SQL Server 2008).
|
|
|
Is it possible to get a version of the t-sql that I can cut and paste and then execute? It would be useful for this and most other demos.
|
|
|
Very good. Simple and Precise.
|
|
|
Plamen, Adam, I updated the description to simplify!
|
|
|
make it twice as long with another example. I *think* I get it... but would love a bit more reinforcement
|
|
|
Very Good. Simple & solid example.
|
|
|
Good job, but I had a little trouble with accent. Lack of exposure on my part, I suppose.
|
|
|
Harvey, the sample scripts used in the video are attached now under References.
|
|
Narasmha Rao on 1/28/2009
good
|
|
|
another example may have helped but I think I get the idea.
|
|
Tom Grumbling on 2/28/2009
It would have been helpful to have an example of what the update statement would be if you hadn't used the CTE
|
|
|
Tom, using the T-SQL specific UPDATE with JOIN (without CTE), the query will look like this:
UPDATE Products
SET product_desc = U.product_desc,
price = U.price
FROM products AS P
JOIN ProductUpdates AS U
ON P.sku = U.sku
WHERE U.effective_date < CURRENT_TIMESTAMP;
|
|
|
its good
|
|
|
Great video!
|
|
|
UPDATE a
SET some_value = 200
--select *
FROM TableA a
JOIN TableB b
on b.some_value = a.some_value
WHERE a.some_other_value = 400
Highlight from SELECT down to view the affected rows, highlight the entire UPDATE statement to affect the UPDATE.
You don't have to use a CTE to preview the affected rows.
|
|
|
The formatting got mangled.
|