Nested case in Mysql

Question

I need to write a SQL statement

if TC is greater than 1 return 500
if TC is greater than .5 return 200
if TC is greater than .25 return 100
if TC is less than .25 return 50.

I know it's a nested case, using MySQL

EDIT: If table two has columns Id, TC, how do I use above to update the TC value.

Joe Stefanelli · Accepted Answer · 2011-02-15T15:52:17.283

3

select case when tc > 1 then 500
            when tc > .5 then 200
            when tc > .25 then 100
            else 50
       end
    ...

EDIT: Based on comments from OP

update YourTable
    set tc = case when tc > 1 then 500
                 when tc > .5 then 200
                 when tc > .25 then 100
                 else 50
             end

edited Feb 15 '11 at 15:52

answered Feb 15 '11 at 15:36

Joe Stefanelli

132,803
19
237
235

+1, no need for a nested case statement as long as the conditions are in the right order. – Eric Petroelje Feb 15 '11 at 15:38
thanks, If table two column Id, TC, how do i use above to update the TC value – Merlin Feb 15 '11 at 15:42
@user428862: I've added the update statement example to my answer. – Joe Stefanelli Feb 15 '11 at 15:52

Nested case in Mysql

1 Answers1