Change Set clause of Update Statement dynamically based on some condition.

Question

Post reply

Change Set clause of Update Statement dynamically based on some condition.

devsql123

SSC-Addicted

Points: 478
More actions
May 27, 2015 at 7:06 am

#304290

Hi Friends,
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @cir AS tmp On t.RowId = tmp.Max_RowId
AND tmp.ReportDate <> '2014-05-29'
Join TD As td on t.CompanyId = td.CompanyId
And t.IMNId = td.IMNId
And t.ReportDate = td.YDate
And Isnull(td.IsPosted, 0) = 0
And td.YDate <> '2014-05-29'
But CASE statement is not working...So any suggestion/help would be appreciated....
Thanks
Devsql

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

UPDATE t SET

t.AODYD = CASE WHEN ISNULL(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END,

t.DAODYD = CASE WHEN ISNULL(td.IsPosted, 0) <> 0 THEN td.ODYD ELSE t.DAODYD END

FROM #ReportData As t

INNER JOIN @cir AS tmp

On t.RowId = tmp.Max_RowId

AND tmp.ReportDate <> '2014-05-29'

INNER JOIN TD As td

on t.CompanyId = td.CompanyId

And t.IMNId = td.IMNId

And t.ReportDate = td.YDate

And Isnull(td.IsPosted, 0) = 0

--And td.YDate <> '2014-05-29'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

devsql123 (5/27/2015)
Hi Friends,
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @cir AS tmp On t.RowId = tmp.Max_RowId
AND tmp.ReportDate <> '2014-05-29'
Join TD As td on t.CompanyId = td.CompanyId
And t.IMNId = td.IMNId
And t.ReportDate = td.YDate
And Isnull(td.IsPosted, 0) = 0
And td.YDate <> '2014-05-29'
But CASE statement is not working...So any suggestion/help would be appreciated....
Thanks
Devsql

You can't use a case expression like that. It is used to return a single value, not control the flow of logic like you are trying to do.

Something like this is more along the lines of what you are trying to do.

Update t

Set t.AODYD = CASE WHEN Isnull(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END

, t.DAODYD = CASE WHEN Isnull(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END

From #ReportData As t

Join @cir AS tmp On t.RowId = tmp.Max_RowId

AND tmp.ReportDate <> '2014-05-29'

Join TD As td on t.CompanyId = td.CompanyId

And t.IMNId = td.IMNId

And t.ReportDate = td.YDate

And Isnull(td.IsPosted, 0) = 0

And td.YDate <> '2014-05-29'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dohsan SSCrazy Points: 2972 More actions · Answer 3

~~Would you be able to provide any sample DDL for the tables you are trying to update?~~

You guys are quick today!

devsql123 SSC-Addicted Points: 478 More actions · Answer 4

Thank you very much guys for this solution...But i made little Typo..so this solution needs little bit update...

Currently my 1st Update statement has Isnull(td.IsPosted, 0) = 0 in WHERE condition.

But 2nd Update statement DO NOT have Isnull(td.IsPosted, 0) = 0 in WHERE condition.

(Means we cannot use this condition. If we use this condition, i am not sure about how output will change)

So as suggested by ChrisM@Work in below 2 lines:

UPDATE t SET

t.AODYD = CASE WHEN ISNULL(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END,

t.DAODYD = CASE WHEN ISNULL(td.IsPosted, 0) <> 0 THEN td.ODYD ELSE t.DAODYD END

I cannot use ISNULL(td.IsPosted, 0) <> 0 for t.DAODYD =

I hope you got idea.

If my 2nd Update statement had that same WHERE condition then above solution would be final/complete/best for me.

Thanks

Devsql

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

devsql123 (5/27/2015)
Thank you very much guys for this solution...But i made little Typo..so this solution needs little bit update...
Currently my 1st Update statement has Isnull(td.IsPosted, 0) = 0 in WHERE condition.
But 2nd Update statement DO NOT have Isnull(td.IsPosted, 0) = 0 in WHERE condition.
(Means we cannot use this condition. If we use this condition, i am not sure about how output will change)
So as suggested by ChrisM@Work in below 2 lines:
UPDATE t SET
t.AODYD = CASE WHEN ISNULL(td.IsPosted, 0) = 0 THEN td.ODYD ELSE t.AODYD END,
t.DAODYD = CASE WHEN ISNULL(td.IsPosted, 0) <> 0 THEN td.ODYD ELSE t.DAODYD END
I cannot use ISNULL(td.IsPosted, 0) <> 0 for t.DAODYD =
I hope you got idea.
If my 2nd Update statement had that same WHERE condition then above solution would be final/complete/best for me.
Thanks
Devsql

So, what expression do you want to use?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Since this is two completely different statements, assuming we're putting this into a stored procedure, why not create two stored procedures? You're looking at the need for two different execution plans (two different WHERE clauses) so it's going to be two different statements within SQL Server. You're doing a ton of work to create a dynamic statement only to arrive at the same place you'd be if you had two statements. Just go for two statements. Simpler solutions are usually the more elegant. You're also going to have more tuning opportunities with multiple statements (or certainly, easier tuning opportunities). Having two procedures doesn't hurt SQL Server or your table. I just don't see the benefit of building this out in this fashion.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 7

Having 2 very similar updates like

UPDATE t SET

t.AODYD = <exp1>

FROM <table expression>

WHERE <common predicates> AND <predicate1>

UPDATE t SET

t.DAODYD = <exp2>

FROM <table expression>

WHERE <common predicates> AND <predicate2>

you logically may generalize them like this

UPDATE t SET

t.AODYD = CASE WHEN<predicate1> THEN <exp1> ELSE t.AODYD END,

t.DAODYD =CASE WHEN<predicate2> THEN <exp2> ELSE t.DAODYD END

FROM <table expression>

WHERE <common predicates> AND (<predicate1> OR <predicate2>)

But i second Grant Fritchey's warning about execution plans.