CODEWITHSUNDEEP
sqlsql-server
Sinnerv
Sinnerv

Reputation: 263

Replace column values

I've got this table,

Name       Rating
A            2
B            1 
C            5
D            3
E            1
F            4

and I've got a rating system 

1-Excellent, 2-Very Good, 3-Good, 4-OK, 5-Poor

I was wondering if i could replace the nueric values in the table to get the following result table.

Name       Rating
A         Very Good
B         Excellent
C           Poor
D           Good
E         Excellent
F            OK

Thanks

Upvotes: 2

Views: 489

Answers (5)

vhadalgi
vhadalgi

Reputation: 7189

Try using Choose

IF sql-server 2012

like this:

 select Rating,name,choose(Rating,'Excellent','Very Good','Good','OK','Excellent','Poor') from table

Fiddle Demo

Upvotes: 1

ASP.Net Developer
ASP.Net Developer

Reputation: 331

Update YOURTABLE

set Rating = case when '2' then ' Very good'
            when '3' then ' good' end

Upvotes: -1

Neil Knight
Neil Knight

Reputation: 48597

Use a CASE statement. Of course, this will only work if your column is not set to a numeric value. 

UPDATE tblRatings
SET Rating = CASE WHEN 1 THEN 'Excellent'
                  WHEN 2 THEN 'Very Good'
                  WHEN 3 THEN 'Good'
                  WHEN 4 THEN 'OK'
                  ELSE 'Poor' 
             END

If it is, you'll need to use a SELECT statement;

SELECT CASE WHEN 1 THEN 'Excellent'
            WHEN 2 THEN 'Very Good'
            WHEN 3 THEN 'Good'
            WHEN 4 THEN 'OK'
            ELSE 'Poor' 
       END
  FROM tblRatings

Upvotes: 1

Nightmaresux
Nightmaresux

Reputation: 538

You can use update function :

update yourtable set rating = 'Excellent' where rating = '1'

and zou can do this update for all your ratings

Upvotes: -1

roman
roman

Reputation: 117606

I don't think it's good idea to update your data inplace, it's better to store id of the rating and not text representation of the data. But you can query your table and replace int with text:

select
    Name,
    case Rating
        when 1 then 'Excellent'
        when 2 then 'Very Good,'
        when 3 then 'Good'
        when 4 then 'OK'
        when 5 then 'Poor'
    end as Rating
from <your table>

Or you can create a lookup table and join with it

create table Rating (id int, desc nvarchar(128))

insert into Rating (id, desc)
select 1, 'Excellent' union all
select 2, 'Very good' union all
select 3, 'Good' union all
select 4, 'OK' union all
select 5, 'Poor'

select
    t.Name,
    R.desc as Rating
from <your table> as t
    left outer join Rating as R on R.id = t.Rating

Upvotes: 3

Related Questions