Conditional Inserts SQL

Question

I have the following table schema:

create table SerialNo2(
IncarnationID_UID counter primary key,
Mark integer
);

Now I want to add a new column in the table, let's call it degree. Given that I want to apply an update on production table, I will have to populate the degree column based on the Mark value for all the existing data in the SerialNo2 table. The rule for this is that

if Mark>90 then degree='A',
else degree='B'

How to write a conditional SQL insert that will accomplish the above function?

Answer 1

Define the degree column as being nullable at first, then do an UPDATE:

UPDATE SerialNo2
SET Degree = CASE WHEN Mark > 90 THEN 'A' ELSE 'B' END

Answer 2

Something like this:

UPDATE SerialNo2 SET SerialNo2.Degree = IIf([Mark]>90,"A","B")

Answer 3

First create your new column (ANSI-92 Query Mode syntax):

ALTER TABLE SerialNo2 ADD 
   Degree CHAR(1) DEFAULT 'B' NOT NULL;

If you query the table immediately you will have NULL in the new column... which is a major bug in the engine IMO i.e. it should have set the value to the DEFAULT for each row however we now have a NOT NULL column that contains NULLs!!

So we have to SET the value for every row

UPDATE SerialNo2 
   SET Degree = IIF(Mark > 90, 'A', 'B');

BTW if you have more than one value to test, rather than nested IIFs, you could use the SWITCH keyword, using the TRUE valeu for the 'case else' clause e.g.

UPDATE SerialNo2 
   SET Degree = SWICTH(
                       Mark > 90, 'A', 
                       Mark <= 0, 'X', 
                       TRUE, 'B'
                      );