T-SQL How to set modified date without trigger only if other values actually changed
My overall goal is to update the [StandardUnitCost] field only on the "parts" that need to be updated, and also update the [DateUpdated] field only on the same lines. I am using a tool that synchronizes data by matching only against the [DateUpdated] column, so it is important that it only changes on rows that were also updated by [StandardUnitCost]. It is important that both happen, and only in the same lines. If the [StandardUnitCost] field is updated with the same value as it is currently, then this value has not changed and the [DateUpdated] field should not be updated.
I currently have this as two separate update statements and need to combine 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')
source to share
Doing this with a CTE will allow you to specify the formula once and use it to trigger an 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 operator is fast enough that there can be no difference in the value for the [ModifiedDate] field in the updated rows.
source to share
I believe that you can simplify your query by moving the criteria to be StandardCost
updated to the sentence WHERE
, in which case you will be guaranteed that the corresponding rows are updated and therefore can also confidently assign the flag to 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 seems like the common theme between updating CASE
on StandardUnitCost
is that it will update anytime there is AverageUnitCost
more than 0.010000
.
This will also avoid redundant form updates Set dateupdated = dateupdated
andStandardunitcost = Standardunitcost
source to share