update multiple fields in SQL

Question

I am trying to update my table in SQL 2012 where the CategoryNumber values should be correspondant for the below name values but it seems that my syntax is wrong.

Update mytable
SET mytable.CategoryNumber IN ('02','05','10')
where mytable.name IN ('peter','michael','ralf')

Answer 1

IN is an operator that defines a set of values, thus it's treated like an unordered list. You cannot do that in the manner you are trying to. I'd suggest doing it one by one:

Update mytable
SET mytable.CategoryNumber = '02'
where mytable.name = 'peter';

Update mytable
SET mytable.CategoryNumber = '05'
where mytable.name = 'michael';

Update mytable
SET mytable.CategoryNumber = '10'
where mytable.name = 'ralf';