SQL WITH TEMP AS通过递归形式进行批量更新

--

--开始执行，递归查出该末级科目及所有上级科目

WITH TEMP AS

(

  SELECT fy_cost.CostGUID,fy_cost.ParentGUID FROM fy_cost WITH (NOLOCK) WHERE CostGUID IN ('dff10db2-de87-4b5a-f648-08d918b08ace') --表的主键ID

  UNION ALL

  SELECT T0.CostGUID,T0.ParentGUID FROM TEMP,fy_cost T0 WITH (NOLOCK) WHERE TEMP.ParentGUID=T0.CostGUID --父级ID==子级ID

)

UPDATE fy_YearBudgetPoise SET OccurredCaliberAmount=(OccurredCaliberAmount-6037.74),x_ContractForDirectAmount=(x_ContractForDirectAmount-6037.74) WHERE fy_YearBudgetPoise.YearBudgetPoiseGUID IN

(

  SELECT a.YearBudgetPoiseGUID FROM fy_YearBudgetPoise a WITH (NOLOCK) WHERE a.BUGUID='734e04cc-4a28-4eab-67e3-08d8c8b759f8' AND a.SpecialBusinessUnitGUID='6af78eeb-db02-408e-b41e-08d91e1d06bc' AND a.CostGUID IN

  (

   SELECT TEMP.CostGUID FROM TEMP

  ) 

  AND a.Year='2022'

)