4

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 up if >=31

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Dylan McCann
  • 113
  • 2
  • 5
  • 17

1 Answers1

6

Have a look at the example

SQL Fiddle DEMO

Somethign like

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, remeber that if CurrentCredit is a FLOAT, and not an INT you might want to 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 the corrected request (>= 31 CurrentCredit + CEILING(CurrentCredit * 0.1)) have a look at using 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

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • @Mahmoud Gamal, I dont think you should delete your answer, it was correct as far as the OP specification went. Just maybe mention the difference. – Adriaan Stander Sep 29 '12 at 10:39
  • I made a mistake on the 31+ credits :-\ It was supposed to be multiply the 31+ credits by 10% then add that number to it but round up. Any explanation on how to just do that? – Dylan McCann Sep 29 '12 at 10:51
  • If not that's fine, I understand, you have already been very helpful and I very appreciative. – Dylan McCann Sep 29 '12 at 10:52
  • @DylanMcCann have a look at the update and using CEILING (which rounds up) – Adriaan Stander Sep 29 '12 at 10:57