SQL Server :递归update语句

Question

我对SQL有些陌生，我正在尝试找出在SQL Server 2012中不对update语句进行硬编码的最好方法。

基本上，我有一个具有列(CompanyID, ParentID, ParentPriceAdj, CompanyPriceAdj)的公司(考虑供应链)的分层结构表。每个公司由其父公司分配一个价格调整，该调整修改PartMaster表中的价目表价格，并通过级联父公司到子公司的调整来计算最终价格。

如果父母的价格调整得到更新，我希望这反映到他的所有子公司，以此类推

又名：

当更新给定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

Answer 2

可以在t-sql中使用WITH expression获取给定子记录的所有父记录。并且可以根据您的逻辑相应地更新记录集中的每条记录。

下面是WITH expression的链接--

Answer 3

您有没有考虑过使用光标？这有点开销，但它可能会满足您的需求。如果你不熟悉，下面是一个基本的大纲。它允许您一次提取每个公司ID，进行更改，然后继续。您可以嵌套它们，将它们包含在循环中，或者在它们中包含循环，无论您需要在列表中执行什么操作，直到没有需要注意的操作。

declare @updatedCompanyID Varchar(5)  
DECLARE D_cursor CURSOR FOR Select updatedCompanyID from                             
OPEN D_cursor
FETCH NEXT FROM D_cursor into @updatedCompanyID
WHILE @@FETCH_STATUS =0 
BEGIN
    --Run Your Code
    WHERE parentID = @updatedCompanyID
FETCH NEXT FROM D_cursor into @updatedCompanyID
END
Close D_cursor
DEALLOCATE D_cursor

希望这能对你有所帮助。如果我误解了，我道歉。