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
  • 102,799
  • 24
  • 193
  • 261
Dylan McCann
  • 113
  • 2
  • 5
  • 15

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
  • 156,697
  • 29
  • 278
  • 282
  • @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