問題描述

我的總體目標(biāo)是僅在需要更新的部分"上更新 [StandardUnitCost] 字段，同時僅在相同的行上更新 [DateUpdated] 字段.我使用的工具僅通過匹配 [DateUpdated] 列來同步數(shù)據(jù)，因此重要的是它只在 [StandardUnitCost] 也已更新的行上更改.重要的是兩者都發(fā)生，并且只發(fā)生在同一行.如果 [StandardUnitCost] 字段將更新為與當(dāng)前相同的值，則該值未更改且 [DateUpdated] 字段應(yīng)該不更新.

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.

我目前將此作為兩個單獨(dú)的更新語句，需要幫助將它們組合起來.

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 執(zhí)行此操作將允許您指定一次公式并使用它來觸發(fā)兩個字段的更新:

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] 字段之所以存在，只是因為該語句運(yùn)行得足夠快，其中更新的行上的 [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.

這篇關(guān)于T-SQL 如何在沒有觸發(fā)器的情況下設(shè)置修改日期，僅當(dāng)其他值實際更改時的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！