MYSQL CASE WHEN UPDATE Query

Question

This is my table.

What I need to do is create a CASE WHEN UPDATE query to update

• CurrentCredit +1 if 1-15

• CurrentCredit +2, if 16-30

• CurrentCredit x.10 and round if> = 31

Answer 1

Take a look at an example

SQL Fiddle DEMO

Sort of

UPDATE MyTable SET CurrentCredit = CASE WHEN CurrentCredit BETWEEN 1 AND 15 THEN CurrentCredit + 1 WHEN CurrentCredit BETWEEN 16 AND 30 THEN CurrentCredit +2 WHEN CurrentCredit >= 31 THEN CurrentCredit * 10 ELSE CurrentCredit END 

Also, remember that if CurrentCredit is a FLOAT and not an INT , you can try something like

 UPDATE MyTable SET CurrentCredit = CASE WHEN CurrentCredit >= 1 AND CurrentCredit < 16 THEN CurrentCredit + 1 WHEN CurrentCredit >= 16 AND CurrentCredit < 31 THEN CurrentCredit +2 WHEN CurrentCredit >= 31 THEN CurrentCredit * 10 ELSE CurrentCredit END; 

EDIT

For a corrected request (> = 31 CurrentCredit + CEILING (CurrentCredit * 0.1)) look at CEILING

 UPDATE MyTable SET CurrentCredit = CASE WHEN CurrentCredit BETWEEN 1 AND 15 THEN CurrentCredit + 1 WHEN CurrentCredit BETWEEN 16 AND 30 THEN CurrentCredit +2 WHEN CurrentCredit >= 31 THEN CurrentCredit + CEILING(CurrentCredit * 0.1) ELSE CurrentCredit END; 

SQL Fiddle DEMO