June 4, 2015 at 11:57 am
Is there a way to use a case statement after "SET"? I am currently getting the following..
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
My statement looks something like..
Update <table>
set case when flg = 1 then a.reserve else b.batch end
from <table> a
inner join <table> b
--- leaving off the ON and Where since it is not part of the problem
June 4, 2015 at 12:10 pm
dwilliscp (6/4/2015)
Is there a way to use a case statement after "SET"? I am currently getting the following..Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
My statement looks something like..
Update <table>
set case when flg = 1 then a.reserve else b.batch end
from <table> a
inner join <table> b
--- leaving off the ON and Where since it is not part of the problem
Really should post your code. From what I see above you have a syntax error with the code.
June 4, 2015 at 12:12 pm
Lynn... it looks like you can not use a case statement inside of the update statement... just trying to make sure.. I guess I can put the update together in a select.. into #a, and then update #a instead.
June 4, 2015 at 12:13 pm
dwilliscp (6/4/2015)
Is there a way to use a case statement after "SET"? I am currently getting the following..Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
My statement looks something like..
Update <table>
set case when flg = 1 then a.reserve else b.batch end
from <table> a
inner join <table> b
--- leaving off the ON and Where since it is not part of the problem
Are you choosing which column to update (two cases required) or which value to use for one column?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
June 4, 2015 at 12:16 pm
dwilliscp (6/4/2015)
Lynn... it looks like you can not use a case statement inside of the update statement... just trying to make sure.. I guess I can put the update together in a select.. into #a, and then update #a instead.
Yes, you can. Please post your actual code and we can help fix it.
June 4, 2015 at 12:17 pm
ChrisM@home (6/4/2015)
dwilliscp (6/4/2015)
Is there a way to use a case statement after "SET"? I am currently getting the following..Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
My statement looks something like..
Update <table>
set case when flg = 1 then a.reserve else b.batch end
from <table> a
inner join <table> b
--- leaving off the ON and Where since it is not part of the problem
Are you choosing which column to update (two cases required) or which value to use for one column?
The syntax for using CASE in updates should be like:
UPDATE <Table>
SET ColumnName = (CASE when flg = 1 then a.reserve else b.batch END)
FROM .....................
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2015 at 12:18 pm
dwilliscp (6/4/2015)
Lynn... it looks like you can not use a case statement inside of the update statement... just trying to make sure.. I guess I can put the update together in a select.. into #a, and then update #a instead.
You definitely can use a case statement in an update, you just have set case.... in your psuedo code, it should be set <column name> = case....
June 5, 2015 at 6:54 am
Lynn Pettis (6/4/2015)
dwilliscp (6/4/2015)
Lynn... it looks like you can not use a case statement inside of the update statement... just trying to make sure.. I guess I can put the update together in a select.. into #a, and then update #a instead.Yes, you can. Please post your actual code and we can help fix it.
UPDATE A
SET Reserve_Pct = case when y.Reserve_Pct_Overwritten = 1
then y.[Reserve_Pct] else a.[Reserve_Pct] end Reserve_Pct
, [Explanation] = y.[Explanation]
, [Last_Update_User] = y.[Last_Update_User]
, [Last_Update_DT] = y.[Last_Update_DT]
, [Batch_Category] = y.[Batch_Category]
, [Batch_Reason] = y.[Batch_Reason]
, [Batch_Due_Date] = y.[Batch_Due_Date]
, [Batch_Highlight] = y.[Batch_HighLight]
, [Reserve_Value] = y.[Reserve_Pct] * A.[Tot_Value_Inv_Group]
FROM ztb_MSC_Epoxy_SLOB_Details A
JOIN ( select
h.[Plant-Material]
, h.[Batch_Num]
, h.[Period]
, h.[Reserve_Pct]
, h.[Explanation]
, h.[Tot_Value_Inv_Group]
, h.[Reserve_Value]
, h.[Last_Update_User]
, h.[Last_Update_DT]
, h.[Batch_Category]
, h.[Batch_Reason]
, h.[Batch_Due_Date]
, h.[Batch_Highlight]
, h.Reserve_Pct_Overwritten
from ztb_MSC_Epoxy_SLOB_Details_Comment_Hist h
inner join (
select
[Plant-Material]
, [Batch_Num]
, max([Period]) as [Last_Entry]
from [ztb_MSC_Epoxy_SLOB_Details_Comment_Hist]
group by [Plant-Material]
, [Batch_Num]
) i on h.[Plant-Material] = i.[Plant-Material]
and h.[Batch_Num] = i.[Batch_Num]
and h.[Period] = i.[Last_Entry]
) Y on A.[Plant-Material] = Y.[Plant-Material]
and A.[Batch_Num] = Y.[Batch_Num]
and A.[Period] = @DATE
This works just fine:
select case when y.Reserve_Pct_Overwritten = 1
then y.[Reserve_Pct] else a.[Reserve_Pct] end as Reserve_Pct
, a.Reserve_Pct as a_reserve_pct
, y.Reserve_Pct as y_reserve_pct
, [Explanation] = y.[Explanation]
, [Last_Update_User] = y.[Last_Update_User]
, [Last_Update_DT] = y.[Last_Update_DT]
, [Batch_Category] = y.[Batch_Category]
, [Batch_Reason] = y.[Batch_Reason]
, [Batch_Due_Date] = y.[Batch_Due_Date]
, [Batch_Highlight] = y.[Batch_HighLight]
, [Reserve_Value] = y.[Reserve_Pct] * A.[Tot_Value_Inv_Group]
, y.Reserve_Pct_Overwritten
into #update
FROM ztb_MSC_Epoxy_SLOB_Details A
JOIN ( select
h.[Plant-Material]
, h.[Batch_Num]
, h.[Period]
, h.[Reserve_Pct]
, h.[Explanation]
, h.[Tot_Value_Inv_Group]
, h.[Reserve_Value]
, h.[Last_Update_User]
, h.[Last_Update_DT]
, h.[Batch_Category]
, h.[Batch_Reason]
, h.[Batch_Due_Date]
, h.[Batch_Highlight]
, h.Reserve_Pct_Overwritten
from [zemeter.net].dbo.ztb_MSC_Epoxy_SLOB_Details_Comment_Hist h
inner join (
select
[Plant-Material]
, [Batch_Num]
, max([Period]) as [Last_Entry]
from [ztb_MSC_Epoxy_SLOB_Details_Comment_Hist]
group by [Plant-Material]
, [Batch_Num]
) i on h.[Plant-Material] = i.[Plant-Material]
and h.[Batch_Num] = i.[Batch_Num]
and h.[Period] = i.[Last_Entry]
) Y on A.[Plant-Material] = Y.[Plant-Material]
and A.[Batch_Num] = Y.[Batch_Num]
and cast(A.[Period] as date) = '6/3/2015'
June 5, 2015 at 7:03 am
dwilliscp (6/5/2015)
UPDATE ASET Reserve_Pct = case when y.Reserve_Pct_Overwritten = 1
then y.[Reserve_Pct] else a.[Reserve_Pct] end
That will work fine.
The error was because you left in a column alias from when it was a SELECT.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2015 at 2:38 pm
Oops. Didn't see it had been taken care of!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 5, 2015 at 5:51 pm
GilaMonster (6/5/2015)
dwilliscp (6/5/2015)
UPDATE ASET Reserve_Pct = case when y.Reserve_Pct_Overwritten = 1
then y.[Reserve_Pct] else a.[Reserve_Pct] end
That will work fine.
The error was because you left in a column alias from when it was a SELECT.
Grrr.... going to go bang my head against the wall... bloody heck. Thanks for the help!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply