How do I update "case when" kogi mosi

Question

update [dbo].[student]
set marks = case
when marks > 60 then 'Good'
when marks >= 40 and marks <= 60 then 'Okay'
when marks < 40 then 'Fail' end
--where marks in (60, 40)

select * from student

I already have a table with some sample data. When I execute this I get: Error converting data type varchar to numeric.

Does this answer your question? [SQL Server : error converting data type varchar to numeric](https://stackoverflow.com/questions/14153665/sql-server-error-converting-data-type-varchar-to-numeric) — Ergis, Dec 12 '21 at 12:41

score 2 · Answer 1 · answered Dec 12 '21 at 12:40

2

Your marks variable is initially a number which is how you're comparing it to values such as 60 or 40.

The issue you're having is you're trying to SET marks to 'Good' or 'Okay' which is a varchar.

answered Dec 12 '21 at 12:40

Joe

99
5

score 0 · Accepted Answer · answered Dec 12 '21 at 12:49

Aside from the issue you have of trying to update the same column you are comparing with using a string data type, what you should probably consider is either putting this logic in a view, or you could add a computed / generated column.

The syntax might vary [slightly] depending on your RDBMS.

Alter table dbo.Student
add [column] Results as
case
 when marks > 60 then 'Good'
 when marks >= 40 and marks <= 60 then 'Okay'
 else 'Fail' 
end

How do I update "case when" kogi mosi

2 Answers2