The CTE result set is derived from a simple query and is referenced by UPDATE statement.Common table expressions can also be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements.Oh, and that new friend you made that’s also named Ross Geller.
Therefore if more than one column in the row requires an update, they will all be updated.
Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement.
You may come across fewer opportunities to use this second technique efficiently.
This query will almost invariably result in a table scan because we are selecting on multiple columns that are unlikely to all be in a covering index.
If we code our update like the example below, then the table will only need to be read once.
For large tables, this can save us a lot of disk IO, especially if the query requires a table scan over a long table Note that there is a definite "top-down" priority involved in the CASE statement.
In the example I am going to knock 25% off all business books from any publisher, and 10% off any non-business books from a particular publisher.
You might be tempted to wrap two separate update statements into one transaction like this: The down side of this technique is that it will read through the table twice, once for each update.
A wise man* once said: So no-one told you life was gonna be this way. A wise man* once said: So no-one told you life was gonna be this way.