SQL Server : recursive update statement

Question

我对 SQL 有点陌生，我正在尝试找出在 SQL Server 2012 中不硬编码更新语句的情况下执行此操作的最佳方法。

基本上，我有一个公司分层表(想象一下供应链)，其中包含列(CompanyID、ParentID、ParentPriceAdj、CompanyPriceAdj)。每个公司都会由其母公司分配一个价格调整，该调整会修改 PartMaster 表中的标价，并通过将父公司的调整级联到子公司来计算最终价格。

如果母公司的价格调整得到更新，我希望其能够反射(reflect)到他的所有子公司等

又名:

更新给定 updatedcompanyID 的 CompanyPriceAdj 时，我想递归查找子 CompanyID 的 (ParentId = UpdatedCompanyID )并将其 ParentPriceAdj 更新为 ParentCompany 的 (parentPriceAdj * (1 + CompanyPriceAdj))

CompanyId     ParentID     ParentPriceAdj    CompanyPriceAdj
  5               6              0.96               .10
  6               8              1                  .20
  7               6              0.96               .15
  8              11              1                   0
 10               6              0.96                0
 11              12              1                   0

我正在考虑使用一个存储过程，该存储过程会更新，然后为刚刚更新的每个子项重复调用自身，然后随后更新他的子项......直到公司没有子项

我尝试环顾四周，找不到任何这样的例子

这就是我现在拥有的

ALTER PROCEDURE [dbo].[UpdatePricing] 
@updatedCompanyID int, @PriceAdj decimal
AS
BEGIN
SET NOCOUNT ON;

    WHILE (Select CompanyID From CompanyInfo Where ParentID = @updatedCompanyID) IS NOT NULL
       UPDATE CompanyInfo 
       SET  ParentPriceAdj = @PriceAdj * (1+CompanyPriceAdj), 
            @updatedCompanyId = CompanyID, 
            @PriceAdj = CompanyPriceAdj         
       WHERE ParentID = @updatedCompanyID

       --- some method to call itself again for each (@updatedCompanyID, @PriceAdj)
END

Answer 1

递归 CTE 可用于遍历层次结构，例如:

ALTER PROCEDURE [dbo].[UpdatePricing]
(
    @companyID int,
    @PriceAdj decimal
)
as
begin
    set nocount on

    update CompanyInfo
    set CompanyPriceAdj = @PriceAdj
    where CompanyID = @companyID

    ;with Hierarchy(CompanyID, ParentID, InPriceAdj, OutPriceAdj)
    as (
        select D.CompanyID, D.ParentID, cast(D.ParentPriceAdj as float),
            cast(D.ParentPriceAdj as float) * cast(1 + D.CompanyPriceAdj as float)
        from CompanyInfo D
        where CompanyID = @companyID
        union all
        select D.CompanyID, D.ParentID,
            H.OutPriceAdj, H.OutPriceAdj * (1 + D.CompanyPriceAdj)
        from Hierarchy H
            join CompanyInfo D on D.ParentID = H.CompanyID
    )
    update D
    set D.ParentPriceAdj = H.InPriceAdj
    from CompanyInfo D
        join Hierarchy H on H.CompanyID = D.CompanyID
    where
        D.CompanyID != @companyID

end