|
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.
|
|
|
Hm... tried to extend this technique to INSERT (by UNION of new records to that Update table), kind of tried to mimic a MERGE statement... and got an error message "Update or insert of view or function [CTE table name] failed because it contains a derived or constant field. Why so? Can it be done (UPDATE + INSERT using CTE)?
|
|
|
Tatyana, not sure what you are trying to do. A CTE can be consumed by only one query, so you cannot use both UPDATE and INSERT in the same query, it has to be one or the other. Please post you query to clarify the intent. The error that you posted indicates something else, that you have a derived column on which you cannot perform update/insert. If you use UNION inside the CTE to add rows then this really does not act like insert, it just adds rows to the CTE but not to the permanent table/view.
|
|
|
Hi Plamen, thanks for answering! Here is my query, below. It works the way you introduced in your sample, but if to lift a comment from a commented part it generates the error I mentioned.
DECLARE @TableMain table (item_ID int NOT NULL, item_name varchar(100))
DECLARE @TableUp table (item_ID int NOT NULL, item_name varchar(100))
INSERT INTO @TableMain VALUES(1, 'aaa'), (2, 'bbb'), (3, 'ccc')
INSERT INTO @TableUp VALUES(1, 'aaa'), (2, 'xxx'), (3, 'cccX'), (4, 'ddd')
;WITH Update_CTE AS
(SELECT TM.item_name AS item_name,
TU.item_name AS new_item_name
FROM @TableUp TU JOIN @TableMain TM
ON TU.item_ID = TM.item_ID
-----------------
--UNION
--SELECT TM.item_name AS item_name, TU.item_name AS new_item_name
--FROM @TableUp TU LEFT OUTER JOIN @TableMain TM
--ON TU.item_ID = TM.item_ID WHERE TM.item_ID IS NULL
------------------
)
UPDATE Update_CTE SET item_name = new_item_name
SELECT * FROM @TableMain
|
|
|
...and I see that there is an objection to a UNION; however, with a FULL JOIN (see below) it updates only the first set of records... I wonder if INCERT viw CTE is possible at all.
DECLARE @TableMain table (item_ID int NOT NULL, item_name varchar(100))
DECLARE @TableUp table (item_ID int NOT NULL, item_name varchar(100))
INSERT INTO @TableMain VALUES(1, 'aaa'), (2, 'bbb'), (3, 'ccc')
INSERT INTO @TableUp VALUES(1, 'aaa'), (2, 'xxx'), (3, 'cccX'), (4, 'ddd')
;WITH Update_CTE AS
(SELECT ISNULL(TM.item_ID, TU.item_ID) AS item_ID, TM.item_name AS item_name,
TU.item_name AS new_item_name
FROM @TableUp TU FULL JOIN @TableMain TM
ON TU.item_ID = TM.item_ID
)
UPDATE Update_CTE SET item_name = new_item_name
SELECT * FROM @TableMain
|
|
|
...Hey, but it updates AND inserts records in the second set (@TableUP in my sample)! Very interesting! I'm apologizing for turning this comments section into a discussion, but your sample is just too inspiring! :) Thank you!
|
|
|
Tatyana, in your first query you cannot use union, because then the data set defined by the CTE is not updatable. In your second query the FULL OUTER JOIN works. But I am not sure what you mean that it inserts and updates at the same time. Both of your source tables have the same rows after the UPDATE, only some values are updated. What is the final result that you are trying to accomplish?
|
|
vin lawrence on
11/9/2010
Difficult to understand his accent should talk slower
|
|
|
Good demo
|
|
|
Very good video
|
|
|
Would it be possible to have written transcripts of these videos?
|
|
Kasey Wheeler on
11/9/2010
Good video.
|
|
|
slow down a bit...a lot of code here. Good stuff though.
|
|
|
It would be great if it would be explained a little bit more in detail.
|
|
Steve Harris on
11/9/2010
Very good and simeple example. One additional step that could be shown is how to clean up the product updates table to remove the records that had been processed.
|
|
Tom Hamilton on
11/9/2010
Good clear example - thanks
|
|
|
try doing a demo of how to update or insert new rows into destination table from a source table but only if the row is new or if only certain of the values in the matching rows have changed, regardless whether other values in the matching rows have changed or not. HINT: use the EXCEPT predicate with a CTE.
|
|
|
Simply superb....
|
|
|
I need more experience to understand, but presentation was still simple and eaasy to get.
|
|
|
Great video!
|
|
|
I have never seen CTE before. It does make things much more clear than using the where clause in the update statement. Plus you can verify how it will work by running only the select statement before committing any changes to the database.
|
|
|
Good content, well presented. I can't think of anything to complain about. A+
|
|
|
Was a little confusing at first, but I got it and am eager to use it!
|
|
|
Would've appreciated a little more detail regarding why the UPDATE query was applied to the Product table...
|
|
|
Like the thinking
|
|
Brian Savoie on
11/9/2010
Very useful in everyday life!
|
|
|
Learned new update type
|
|
|
Very helpful, thanks!
|
|
Stevan Cohen on
11/15/2010
Too fast, hard to follow.
Would help to have text version of this code.
|
|
Plamen Ratchev on
11/16/2010
Stevan, in the References section you can press the Example Script link and download the text for all code used in this video.
|
|
|
Thanks
|
|
Max Turavani on
6/16/2011
very good !
|
|
rupesh sharma on
6/16/2011
Really useful, thanks
|
|
Terry Phelps on
6/16/2011
Obviously this video was created a few years ago considering the dates used for the update statements.
|
|
|
Thanks useful tidbit on CTE's for updates
|
|
|
Good way to show how to use the CTE syntax with updates. It would have been helpful to show the query without using the CTE to illustrate why/when you'd want to use a CTE vs. the typical syntax.
|
|
Samson J. Loo on
6/16/2011
Perfect. Exactly what I was looking for.
|
|
|
what about performance? Can you give example which shows about performance.
|
|
|
Great tip!
|
|
Dennis Allen on
6/16/2011
-- this seems just as clear to me...
UPDATE U
SET U.product_desc = N.product_description, U.price = N.price
FROM Products U
JOIN ProductUpdates N ON U.sku = N.sku AND N.effective_date < CURRENT_TIMESTAMP;
|
|
|
gud 1
|
|
|
good & informative - thanks
|
|
|
Great basic overview of a neat way to use a CTE. Nice job.
|
|
|
Creative idea
|
|
|
Nice one to kick up using CTE.
|
|
|
This is great, I never knew you could do something like that.
|
|
|
Additional Examples for more complicated operation would have been appreciated. Little more time should be spent on the Syntax of the CTE.
|
|
|
short knowledge piece. very nice
|
|
|
Extremely relevent and very helpful. Thanks a ton.
|
|
|
As someone who has never seen CTEs before, this was much too fast to absorb.
|
|
|
Excellent!!!
|
|
|
The information seems to be trivial..we expect more advanced things to spare our time for watching
|
|
|
Vijay, performance would be the same as using UPDATE with FROM clause. Best in each case is to look at the execution plan.
|
|
|
Had difficulty understanding the presenter's English.
|
|
|
Have a good one
|
|
|
good demo. also leant from the replies....
|
|
Maurice Ivory on
11/10/2011
This was good
|
|
|
Find it really hard to understand what Plamen is saying
|