DML_Modifying Data Through Table Expressions_UPDATE

DML_Modifying Data Through Table Expressions_UPDATE
之前也学习过使用CTE，再来泛泛的学习下，
最后将会将一些书籍上或学习到的CTE来个小结

/*
     Microsoft SQL Server 2008 T-SQL Fundamentals_CN
     Microsoft SQL Server 2008 T-SQL Fundamentals
*/

--以Northwind库下的Customers表为例，说明CTE的用法
SELECT *
FROM Customers
------------------------------------------------------------------------------------------
--它的内部查询返回所有来自 USA 的客户，外部查询则选择了CTE中的所有行
--与派生表一样，一旦外部查询完成，CTE的声明期也就结束了     （将会专开一篇学习Derived Table）

WITH <CTE_Name>[(<target_column_list>)]
AS
(
<inner_query_defining_CTE>
)
<outer_query_against_CTE>


WITH USACusts AS
(
    SELECT CustomerID,CompanyName
    FROM Customers
    WHERE Country = N'USA'
)
SELECT * FROM USACusts;
---------------------
--根据语法增加了WITH 后面表的字段
WITH USACusts(CustomerID,CompanyName,Country) AS
(
    SELECT CustomerID,CompanyName, Country
    FROM Customers
    WHERE Country = N'USA'
)
SELECT * FROM USACusts;
------------------------------------------------------------------------------------------
--使用CTE（Common Table Expressions）

--下面是上次DML_Data Modification_UPDATE中带有联接的更新语句
UPDATE OD
   SET discount = discount + 0.03
FROM dbo.[Order Details] AS OD
    JOIN dbo.Orders AS O
    ON OD.OrderID = O.OrderID
WHERE CustomerID = 'TOMSP';
----------------
SELECT * FROM [Order Details]
SELECT * FROM Orders
----------------
--上面是带有联接的更新语句，将它改变...
--不记得字段内容了，可用上面2句查询一下
SELECT CustomerID,
             OD.OrderID,
             productid,
             discount,
             discount+0.05 AS newdiscount
   --SET discount = discount + 0.03
FROM dbo.[Order Details] AS OD
    JOIN dbo.Orders AS O
    ON OD.OrderID = O.OrderID
WHERE CustomerID = 'TOMSP';
----------------
--继续改编，将上面的语句用 WITH包含起来（使用CTE的例子），然后再 UPDATE
--SQL Server 2005以后的版本支持CTE(没测试过，本环境为MSSQL2014)
WITH C AS
(
    SELECT CustomerID,
                 OD.OrderID,
                 productid,
                 discount,
                 discount+0.05 AS newdiscount
       --SET discount = discount + 0.03
    FROM dbo.[Order Details] AS OD
        JOIN dbo.Orders AS O
        ON OD.OrderID = O.OrderID
    WHERE CustomerID = 'TOMSP'
)
UPDATE C SET discount = newdiscount;
------------------------------------------------------------------------------------------
--使用派生表的例子( Derived Table)
--SQL Server 2005以前的版本支持派生表(没测试过，本环境为MSSQL2014)
--Northwind库
UPDATE D
    SET discount = newdiscount
FROM (