问题描述

我的总体目标是仅在需要更新的部分"上更新 [StandardUnitCost] 字段，同时仅在相同的行上更新 [DateUpdated] 字段.我使用的工具仅通过匹配 [DateUpdated] 列来同步数据，因此重要的是它只在 [StandardUnitCost] 也已更新的行上更改.重要的是两者都发生，并且只发生在同一行.如果 [StandardUnitCost] 字段将更新为与当前相同的值，则该值未更改且 [DateUpdated] 字段应该不更新.

My overall goal is to update the [StandardUnitCost] field only on "parts" that need to be updated while also updating the [DateUpdated] field only on those same lines. I am using a tool that syncs the data by matching up to the [DateUpdated] column only, so it is important that it only changes on lines that the [StandardUnitCost] has also updated on. It is important that both happen, and only on the same rows. If the [StandardUnitCost] field will be updated with the same value that it currently has, then the value has not changed and the [DateUpdated] field should not be updated.

我目前将此作为两个单独的更新语句，需要帮助将它们组合起来.

I currently have this as two separate update statements and need help combining them.

Update [mas_wgd].[dbo].[CI_Item]
  Set dateupdated = CASE
   WHEN StandardUnitCost < AverageUnitCost then convert (date, GETDATE())
   WHEN (AverageUnitCost + 2) >= 22.0
    AND standardunitcost > (AverageUnitCost + 2.000000) then convert (date, GETDATE())
   When StandardUnitCost < 22.000000
    And StandardUnitCost > 0 then convert (date, GETDATE())
  Else dateupdated
end
Where ProductLine IN ('A010', 'A020', 'A030', 'A040', 'A050', 'A060', 'A070', 'A080',
      'A090', 'A100', 'A110', 'A120', 'A130', 'A130', 'A140', 'A150', 'A200', 'A250',
      'A300', 'A350', 'A400', 'A450', 'A500', 'A550', 'A600', 'AGNC', 'C010', 'C020',
      'C030', 'C040', 'C050', 'C060', 'C070', 'C080', 'C090', 'C100', 'C110', 'C120',
      'C130', 'C130', 'C140', 'C150', 'C200', 'C250', 'C300', 'C350', 'C400', 'C450',
      'C500', 'C550', 'C600', 'CGNC')


Update [mas_wgd].[dbo].[CI_Item]
 Set Standardunitcost = CASE
  WHEN (AverageUnitCost between 0.010000 and 22.000000) Then  22.00000 
  WHEN AverageUnitCost > 22.000000 then AverageUnitCost + 2.000000
 Else StandardUnitCost 
end
Where ProductLine IN ('A010', 'A020', 'A030', 'A040', 'A050', 'A060', 'A070', 'A080',
      'A090', 'A100', 'A110', 'A120', 'A130', 'A130', 'A140', 'A150', 'A200', 'A250',
      'A300', 'A350', 'A400', 'A450', 'A500', 'A550', 'A600', 'AGNC', 'C010', 'C020',
      'C030', 'C040', 'C050', 'C060', 'C070', 'C080', 'C090', 'C100', 'C110', 'C120',
      'C130', 'C130', 'C140', 'C150', 'C200', 'C250', 'C300', 'C350', 'C400', 'C450',
      'C500', 'C550', 'C600', 'CGNC')

推荐答案

通过 CTE 执行此操作将允许您指定一次公式并使用它来触发两个字段的更新:

Doing this via a CTE will allow you to specify the formula once and use it to trigger the update on both fields:

SET NOCOUNT ON;
SET ANSI_NULLS ON;

DECLARE @Test TABLE (ID INT NOT NULL IDENTITY(1, 1),
                     AverageUnitCost INT NULL,
                     StandardUnitCost INT NULL,
                     ModifiedDate DATETIME,
                     WasUpdated BIT NULL);
INSERT INTO @Test VALUES (5, 8, GETDATE(), 0);
INSERT INTO @Test VALUES (15, 11, GETDATE(), 0);
INSERT INTO @Test VALUES (12, 35, GETDATE(), 0);
INSERT INTO @Test VALUES (22, 47, GETDATE(), 0);

SELECT * FROM @Test;

;WITH cte AS
(
  SELECT tmp.ID,
         tmp.StandardUnitCost,
         tmp.ModifiedDate,
         tmp.WasUpdated,
         CASE WHEN tmp.AverageUnitCost BETWEEN 10 AND 20 THEN 22
              WHEN tmp.AverageUnitCost > 20 THEN (tmp.AverageUnitCost + 2)
              ELSE tmp.StandardUnitCost
         END AS [NewValue]
  FROM @Test tmp
  --WHERE ProductLine IN ('A010'...) -- this is part of the real table so uncomment
)
UPDATE tab
SET    tab.StandardUnitCost = tab.NewValue,
       tab.ModifiedDate = GETDATE(),
       tab.WasUpdated = 1 -- only here to clearly indicate that the row was updated
FROM   cte tab
WHERE  tab.StandardUnitCost <> tab.NewValue;

SELECT * FROM @Test;

[WasUpdated] 字段之所以存在，只是因为该语句运行得足够快，其中更新的行上的 [ModifiedDate] 字段的值可能没有差异.

The [WasUpdated] field is only there because the statement runs fast enough where there might not be a difference in value for the [ModifiedDate] field on rows that were updated.