CODEWITHSUNDEEP
sql-servert-sql
Xestius
Xestius

Reputation: 73

T-SQL How to set modification date without Trigger only if other values actually changed

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')

Upvotes: 3

Views: 466

Answers (2)

StuartLC
StuartLC

Reputation: 107327

I believe you can simplify your query by moving the criteria for StandardCost updating to the WHERE clause, in which case you will be guaranteed that the matching rows will be updated and hence can also confidently timestamp the dateupdated column at the same time:

Update [mas_wgd].[dbo].[CI_Item]
  Set dateupdated = convert (date, GETDATE()),
  Standardunitcost = 
    CASE
       WHEN (AverageUnitCost between 0.010000 and 22.000000) Then  22.00000 
       WHEN AverageUnitCost > 22.000000 then AverageUnitCost + 2.000000
    END -- CASE
Where ProductLine IN ('A010', ...  'CGNC')
AND  (AverageUnitCost > 0.010000);

It appears that the common theme between the CASE update on StandardUnitCost is that it will be updated any time that AverageUnitCost is greater than 0.010000.

This will also avoid redundant updates of the form Set dateupdated = dateupdated and Standardunitcost = Standardunitcost

Upvotes: 2

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

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;

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.

Upvotes: 3

Related Questions