T-SQL add to existed stored procedure to check null value and update data!

• ATroung

  SSC Enthusiast

  Points: 164

  More actions

  June 16, 2007 at 10:43 pm

  #179377

  Hello all,

  My T-SQL question implement for checking and updating the existed data in SQL server table of 700000 records. Details are described belowed:

  My sample table as following:

  ID | lastname | firstname | school | examscore | areacode | grade |

  -------------------------------------------------------------------

  1  | Snow      | Dun        | east    | 80            | 1           |          |

  2  | Tomy      | Hellman   | east    | 100          |              |          |

  3  | Julie1      | Williams   | west   |                |              |          |

  4  | Jeny       | Lee         |          |                |              |          |

  ...

  First check whether or not any null values in school, examscore, and areacode?

  Then updated data in grade column followed the belowed ordering rules:

  1. If school, examscore and areacode are not null; then need to update grade = A

  2. If school, examscore are not null and areacode is null; then need to update grade = B

  3. If school is not null; examscore and areacode are null; then need to update grade = C

  4. If all school, examscore and areacode are null; then need to update grade = D

  I am not sure the SQL commands that implement above conditions. Any actual T-SQL codes is much appreciated. Thanks in advance.

• Koji Matsumura

  SSCrazy

  Points: 2241

  More actions

  June 17, 2007 at 7:03 pm

  #713481

  UPDATE YourTable

  SET grade =

  CASE

  WHEN school IS NOT NULL AND examscore IS NOT NULL AND areacode IS NOT NULL

  THEN 'A'

  WHEN school IS NOT NULL AND examscore IS NOT NULL AND areacode IS NULL

  THEN 'B'

  WHEN school IS NOT NULL AND examscore IS NULL AND areacode IS NULL

  THEN 'C'

  WHEN school IS NULL AND examscore IS NULL AND areacode IS NULL

  THEN 'D'

  END