SQL Server CE change column type string to int

Question

Users Table:

I want if Role column value is Admin change value to 1,if User change value to 0.After change the type of the column like this Code ALTER TABLE Users ALTER COLUMN Role int;

How can I do this with SQL Server Compact query?

Answer 1

UPDATE Users  
SET Role =  
  CASE  
     WHEN Role = 'Admin' THEN 1 
     WHEN Role = 'User' THEN 0 
  END 

Then

ALTER TABLE Users ALTER COLUMN Role tinyint NOT NULL