Case update statement 3

[aumstu]

I am trying to update a table (EWS) by using a case statement. I am able to run this select statement

select A.EWS_ID, A.EWS_Level, b.Hours_hours_earned,
"NEW STUFF"
= CASE
When (b.Hours_hours_earned) Between 0 and 27.990 Then '01'
When (b.Hours_hours_earned) Between 28 and 57.990 Then '02'
When (b.Hours_hours_earned) Between 58 and 87.990 Then '03'
When (b.Hours_hours_earned) Between 88 and 999 Then '04'
Else 'Something'
End
from EWS A, Hours b
where a.EWS_ID = b.Hours_ID
and EWS_Level = '10'

but i cant figure out how I can do an update with it. Can you have a select statement with an update? Maybe with a begin? Not sure...Thanks

 

[bborissov]

UPDATE MyTable SET MyField = CASE
                                  WHEN xxxx THEN 1
                                  WHEN yyyy THEN 2
                                  ELSE 0 END
WHERE ....

Borislav Borissov

 

[aumstu]

Thnaks for answering....

That update would work great if I was just using one table...but, I need to get the hours from a seperate table.
Any ideas..

Thanks

 

[SQLDenis]

what about

update a set EWS_Level 
= CASE
When (b.Hours_hours_earned) Between 0 and 27.990 Then '01'
When (b.Hours_hours_earned) Between 28 and 57.990 Then '02'
When (b.Hours_hours_earned) Between 58 and 87.990 Then '03'
When (b.Hours_hours_earned) Between 88 and 999 Then '04'
Else 'Something'
End
from EWS A, Hours b
where a.EWS_ID = b.Hours_ID
and EWS_Level = '10'

or

update a set EWS_Level 
= CASE
When (b.Hours_hours_earned) Between 0 and 27.990 Then '01'
When (b.Hours_hours_earned) Between 28 and 57.990 Then '02'
When (b.Hours_hours_earned) Between 58 and 87.990 Then '03'
When (b.Hours_hours_earned) Between 88 and 999 Then '04'
Else 'Something'
End
from EWS A join Hours b
on a.EWS_ID = b.Hours_ID
and EWS_Level = '10'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks SQLDenis..I will give that code a shot and let you know...so far, i set it up like this and it worked out

Update EWS
SET ews_level 
= CASE
When (Select hours_hours_earned from hours where hours_id = ews_id) Between 0 and 27.990 Then '01'
etc....
Else 'Something'
End
where ews_level = '10'

thanks again

 

[aumstu]

Works great sqldenis!

I am liking your code a lot better than mine...much cleaner. Here is a star for yeah

Thanks again.

 

[SQLDenis]

thanks

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/