Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case update statement 3

Status
Not open for further replies.

aumstu

MIS
Jan 20, 2006
40
US
I am trying to update a table (EWS) by using a case statement. I am able to run this select statement
Code:
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
 
Code:
UPDATE MyTable SET MyField = CASE
                                  WHEN xxxx THEN 1
                                  WHEN yyyy THEN 2
                                  ELSE 0 END
WHERE ....

Borislav Borissov
 
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
 
what about
Code:
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
Code:
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:
Personal Blog:
 
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

Code:
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
 
Works great sqldenis!

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

Thanks again.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top