SQL: Change the value of a column in function of another

I get a table from SQL that I put whitin a winform’s datagridview. I just want to change the value of my last column in funtion of another. Here is the example:

I have 4 columns : ID / Price / quantity / Rank

I want to say something like “If ID = 4 then Rank = 5, but if ID = 2 then rank = 2 etc…”

I tried this but that throws an error:

SELECT TOP 1000 ID , Price , quantity , Rank, Country, 
CASE Mytable.ID
WHEN  4
THEN  Mytable.Rank = 4
END 
FROM Mytable

I got “Incorrect syntax near ‘='” It’s like it doesn’t accept the “=”… Any idea ?

Answer

The as goes after the case expression:

SELECT TOP 1000 ID , Price , quantity, Country, 
       (CASE Mytable.ID
          WHEN  4 THEN 4
        END) as rank
FROM Mytable;

If you want both the original rank and the new rank, then give them different aliases:

SELECT TOP 1000 ID, Price, quantity, rank, Country, 
       (CASE Mytable.ID
          WHEN 4 THEN 4
        END) as new_rank
FROM Mytable;