Blog Pages

WITH common_table_expression (CTE)

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

WITH PPP AS
(
    SELECT .....
    FROM ... 
    WHERE .....
    UNION ALL
    SELECT ....
    FROM ..... INNER JOIN .... ON .....
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials c
JOIN PPP d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0


WITH vw AS
 (
    SELECT ....
,r AS
 (
    SELECT .....
)
..........

Use it in update:

No comments:

Post a Comment