March 18, 2010 at 4:44 pm
Trying to update one of several fields by examining each in order and updating the first that meets my criteria.
A Case in a select does it:
Select
CASE
WHEN e.QtdRptEarnSubjDed00 > 0 THEN e.QtdRptEarnSubjDed00 + emp.Diff
WHEN e.QtdRptEarnSubjDed01 > 0 THEN e.QtdRptEarnSubjDed01 + emp.Diff
WHEN e.QtdRptEarnSubjDed02 > 0 THEN e.QtdRptEarnSubjDed02 + emp.Diff
WHEN e.QtdRptEarnSubjDed03 > 0 THEN e.QtdRptEarnSubjDed03 + emp.Diff
END AS NEW_QuarterAMOUNT
But I'm trying to use the same cascading logic in an update
-- pseudo code for what I want it to do
CASE
WHEN QtdRptEarnSubjDed00 > 0 THEN
SET QtdRptEarnSubjDed00 + @diff
WHEN QtdRptEarnSubjDed01 > 0 THEN
SET QtdRptEarnSubjDed01 + @diff
WHEN QtdRptEarnSubjDed02 > 0 THEN
SET QtdRptEarnSubjDed02 + @diff
WHEN QtdRptEarnSubjDed03 > 0 THEN
SET QtdRptEarnSubjDed03 +@diff
END,
In the sample code below, I want the @diff var applied to the the first field in rows 1 & 3, but in field 3 in row 2.
----------------------------------------------------------------------------------------------------------
If Exists(Select 1 from sysobjects where name = 'xxUpdateTest' and type = 'u')
drop TABLE xxUpdateTest
go
create TABLE dbo.xxUpdateTest
(
EmpId char(10) null,
YtdRptEarnSubjDed float null,
QtdRptEarnSubjDed00 float null,
QtdRptEarnSubjDed01 float null,
QtdRptEarnSubjDed02 float null,
QtdRptEarnSubjDed03 float null
)
INSERT INTO dbo.xxUpdateTest
(EmpId,YtdRptEarnSubjDed,QtdRptEarnSubjDed00,QtdRptEarnSubjDed01,QtdRptEarnSubjDed02,QtdRptEarnSubjDed03)
SELECT 'BOB607382',19547.4,3908.2,4826.38,4009.69,6803.13 union
SELECT 'BOC622452',6513, 0, 0,1999, 4514 union
SELECT 'BOD502632 ',18023.42,3351.6,4788, 4428.9,5454.92
DECLARE
@diff float,
@Box1Corr float
SELECT
@diff = 113.7,
@Box1Corr = 212121.82
SELECT
xxUpdateTest.*,
@diff,
@Box1Corr
FROM xxUpdateTest
UPDATE
dbo.xxUpdateTest
-- I want to find the first non Zero value amongst 4 fields, and
--add the variable @diff to that field ONLY.
--this does not work, it updates each field
--I want it to update the first field that meets the criteria
SET
QtdRptEarnSubjDed00 = CASE WHEN QtdRptEarnSubjDed00 > 0 THEN QtdRptEarnSubjDed00 + @diff ELSE QtdRptEarnSubjDed00 end,
QtdRptEarnSubjDed01 = CASE WHEN QtdRptEarnSubjDed01 > 0 THEN QtdRptEarnSubjDed01 + @diff ELSE QtdRptEarnSubjDed01 end,
QtdRptEarnSubjDed02 = CASE WHEN QtdRptEarnSubjDed02 > 0 THEN QtdRptEarnSubjDed02 + @diff ELSE QtdRptEarnSubjDed02 end,
QtdRptEarnSubjDed03 = CASE WHEN QtdRptEarnSubjDed03 > 0 THEN QtdRptEarnSubjDed03 + @diff ELSE QtdRptEarnSubjDed03 end,
--This Select did what I expected,
-- CASE
-- WHEN e.QtdRptEarnSubjDed00 > 0 THEN e.QtdRptEarnSubjDed00 + emp.Diff
-- WHEN e.QtdRptEarnSubjDed01 > 0 THEN e.QtdRptEarnSubjDed01 + emp.Diff
-- WHEN e.QtdRptEarnSubjDed02 > 0 THEN e.QtdRptEarnSubjDed02 + emp.Diff
-- WHEN e.QtdRptEarnSubjDed03 > 0 THEN e.QtdRptEarnSubjDed03 + emp.Diff
-- END AS NEW_QuarterAMOUNT
-- psuedo code for what I want it to do
-- CASE
-- WHEN QtdRptEarnSubjDed00 > 0 THEN
-- SET QtdRptEarnSubjDed00 + @diff
-- WHEN QtdRptEarnSubjDed01 > 0 THEN
-- SET QtdRptEarnSubjDed01 + @diff
-- WHEN QtdRptEarnSubjDed02 > 0 THEN
-- SET QtdRptEarnSubjDed02 + @diff
-- WHEN QtdRptEarnSubjDed03 > 0 THEN
-- SET QtdRptEarnSubjDed03 +@diff
-- END,
YtdRptEarnSubjDed = @Box1Corr
FROM
dbo.xxUpdateTest
SELECT
xxUpdateTest.*,
@diff,
@Box1Corr
FROM xxUpdateTest
----------------------------------------------------------------------------------------------------------
--thanks for any help you can give.
March 18, 2010 at 5:03 pm
First thing you must understand is that CASE is an expression not a statement. All it does is return a value, like a function with IF/THEN logic embedded in it. You are trying to use it to control what SET gets executed, but there is only one SET per UPDATE.
The way to do this with CASE is to update all your columns, but use CASE to control what the new values will be, leaving the current value as the default using the ELSE keyword. Like so:
UPDATE SOMETABLE
SET QtdRptEarnSubjDed00 = CASE WHEN QtdRptEarnSubjDed00 > 0
THEN QtdRptEarnSubjDed00 + @diff
ELSE QtdRptEarnSubjDed00
END,
QtdRptEarnSubjDed01 = CASE WHEN QtdRptEarnSubjDed01 > 0
THEN QtdRptEarnSubjDed01 + @diff
ELSE QtdRptEarnSubjDed01
END,
etc, etc
WHERE blah blah blah
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2010 at 5:11 pm
Thanks. I believe I'm doing what you suggested
QtdRptEarnSubjDed00 = CASE WHEN QtdRptEarnSubjDed00 > 0 THEN QtdRptEarnSubjDed00 + @diff ELSE QtdRptEarnSubjDed00 end,
QtdRptEarnSubjDed01 = CASE WHEN QtdRptEarnSubjDed01 > 0 THEN QtdRptEarnSubjDed01 + @diff ELSE QtdRptEarnSubjDed01 end,
QtdRptEarnSubjDed02 = CASE WHEN QtdRptEarnSubjDed02 > 0 THEN QtdRptEarnSubjDed02 + @diff ELSE QtdRptEarnSubjDed02 end,
QtdRptEarnSubjDed03 = CASE WHEN QtdRptEarnSubjDed03 > 0 THEN QtdRptEarnSubjDed03 + @diff ELSE QtdRptEarnSubjDed03 end,
what I need it to do is ONLY update the first field that meets the criteria, not all of them. The Case statement fell out on the first logical expression that matched, which is what I'm after.
March 18, 2010 at 5:34 pm
My error. I took the pseudo code too literally. How about just growing the WHEN clauses like this? I know it's tedious to have to keep extending each column's logic with additional ANDs, but it will run well in big batches, as well as for individual transactions.
UPDATE SOMETABLE
SET QtdRptEarnSubjDed00 = CASE WHEN QtdRptEarnSubjDed00 > 0
THEN QtdRptEarnSubjDed00 + @diff
ELSE QtdRptEarnSubjDed00
END,
QtdRptEarnSubjDed01 = CASE WHEN QtdRptEarnSubjDed01 > 0
AND QtdRptEarnSubjDed00 <= 0
THEN QtdRptEarnSubjDed01 + @diff
ELSE QtdRptEarnSubjDed01
END,
QtdRptEarnSubjDed02 = CASE WHEN QtdRptEarnSubjDed02 > 0
AND QtdRptEarnSubjDed00 <= 0
AND QtdRptEarnSubjDed01 <= 0
THEN QtdRptEarnSubjDed02 + @diff
ELSE QtdRptEarnSubjDed02
END,
You could also write dynamic SQL based on which column was the first to have a value, but that would be unsuitable for large batches.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2010 at 5:41 pm
Is this more suitable?
Declare @QtdRptEarnSubjDed00 varchar(10)
,@QtdRptEarnSubjDed01 varchar(10)
,@QtdRptEarnSubjDed02 varchar(10)
,@diff varchar(10)
Select @QtdRptEarnSubjDed00 = 0,@QtdRptEarnSubjDed01=0,@QtdRptEarnSubjDed02=1,@diff=20
Declare @sql varchar(max)
Set @sql = 'UPDATE SOMETABLE
SET ' + case WHEN @QtdRptEarnSubjDed00 > 0
THEN 'QtdRptEarnSubjDed00'
When @QtdRptEarnSubjDed01 > 0
Then @QtdRptEarnSubjDed02
Else 'QtdRptEarnSubjDed02'
End
+ ' = '
Set @sql = @sql + Case When @QtdRptEarnSubjDed00> 0
Then 'QtdRptEarnSubjDed00 + ' + @diff +''
When @QtdRptEarnSubjDed01 > 0
Then 'QtdRptEarnSubjDed01 +' + @diff +''
Else 'QtdRptEarnSubjDed02 +' + @diff +''
END
Set @sql = @sql + char(10)+char(13)+'WHERE blah blah blah'
Print @SQL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 5:41 pm
EXCELLENT! Thanks. Sometimes you look at things for too long and from the wrong angle.
March 18, 2010 at 5:43 pm
The Dixie Flatline (3/18/2010)
My error. I took the pseudo code too literally. How about just growing the WHEN clauses like this? I know it's tedious to have to keep extending each column's logic with additional ANDs, but it will run well in big batches, as well as for individual transactions.
UPDATE SOMETABLE
SET QtdRptEarnSubjDed00 = CASE WHEN QtdRptEarnSubjDed00 > 0
THEN QtdRptEarnSubjDed00 + @diff
ELSE QtdRptEarnSubjDed00
END,
QtdRptEarnSubjDed01 = CASE WHEN QtdRptEarnSubjDed01 > 0
AND QtdRptEarnSubjDed00 <= 0
THEN QtdRptEarnSubjDed01 + @diff
ELSE QtdRptEarnSubjDed01
END,
QtdRptEarnSubjDed02 = CASE WHEN QtdRptEarnSubjDed02 > 0
AND QtdRptEarnSubjDed00 <= 0
AND QtdRptEarnSubjDed01 <= 0
THEN QtdRptEarnSubjDed02 + @diff
ELSE QtdRptEarnSubjDed02
END,
You could also write dynamic SQL based on which column was the first to have a value, but that would be unsuitable for large batches.
I didn't see this when I posted - was working on my solution.
I like this solution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 9:03 pm
Thanks to you both!
March 18, 2010 at 9:09 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply