Best way to update the same column without using x update statements (if possible)?

Question

I have a few questions regarding the following scenario -

Consider this table (db<>fiddle here):

create TABLE StudentClass( 
name [nvarchar](30) NULL,
class [nvarchar](100) NULL,
)

insert into StudentClass
values ('Anne', 'Math;Art;Art History'),
('Bill', 'Math;English'),
('Charlie', 'English'),
('Daisy', 'English;Art;Art History'),
('Eddy', 'Math;Art')

I want to replace each class subject with a number code. For example:

Math --> 001
English --> 002
Art --> 003
Art History --> 004

If I try to do this in one update statement, it won't let me do that. The only thing I could think of was four separate statements using replace:

update StudentClass set class = replace(class, 'Math', '001')
update StudentClass set class = replace(class, 'English', '002')
update StudentClass set class = replace(class, 'Art', '003')
update StudentClass set class = replace(class, 'Art History', '004')

This almost works, but not quite. Since "Art History" contains "Art" in it, this update update StudentClass set class = replace(class, 'Art', '003') triggers first and messes up the next update.

My 2 questions are:

First, is there a better way to update the same column instead of making x update statements?

And second, is there a better way to make sure that the Art/ArtHistory replacement doesn't happen? For this, the only thing I could think of was switching the query order so that it looks like this, but I assume there's a better way I don't know about:

update StudentClass set class = replace(class, 'Math', '001')
update StudentClass set class = replace(class, 'English', '002')
update StudentClass set class = replace(class, 'Art History', '004')
update StudentClass set class = replace(class, 'Art', '003')

Appreciate all the help as always!

Answer 1

update StudentClass set class = 
CASE 
  WHEN class LIKE '%Math%' THEN replace(class, 'Math', '001')
  WHEN class LIKE '%English%' THEN replace(class, 'English', '002')
  WHEN class LIKE '%Art History%' THEN replace(class, 'Art History', '004')
  WHEN class LIKE '%Art%' THEN replace(class, 'Art', '003')
  ELSE '000' 
END;

If there are more than one true WHEN condition, you must the more lenght condition before the short one! CASE statement Jump the firs true!

In the question is this updates bad order:

update StudentClass set class = replace(class, 'Art', '003')
update StudentClass set class = replace(class, 'Art History', '004')

Because before second update there isn't any 'Art History' just '003 History'!

Answer 2

Here is how I would approach this, which is VERY similar to Ronan's solution posted before me). Just that I am using a CTE instead of value constructor and have an update statement, which I assumed to be part of your question.

with cte (class, label) as

(select 'Math', '001' union all
 select 'English', '002' union all
 select 'Art', '003' union all
 select 'Art History', '004'),

cte2 as
(select t1.name, t1.class, string_agg(t3.label,';') as new_class
 from StudentClass t1
 cross apply string_split(t1.class,';') as t2
 left join cte t3 on t3.class=t2.value
 group by t1.name, t1.class)

update a
set a.class=b.new_class
from StudentClass a 
join cte2 b on a.name=b.name;

Breakdown of steps:

• Define a small update lookup using a CTE.

• Use string_split to split delimited values into rows and use cross apply to laterally append them to the dataset.

• Use a left join to pull the right update value based on the lookup.

• Use string_agg to aggregate the values.

• Update your main table by joining back to the final CTE

You should really consider stopping at that join after string_split and not stitch it back together with string_agg. It's rarely a good idea to use delimited strings to store multiple values. I understand if you don't have the liberty to change the table design.

Answer 3

In most cases you will probably have a table with the information about the numbers of the classes

In this case you can use JOIN in order to get the result that you need without manually write the names of the options in your query.

With that being said, using small number of consist list of classes numbers and if you do not have that information in table you can use simply CASE statement. THIS MEAN THAT YOU COUNT ON THE ORDER OF THE OPTIONS (when... Then...) in the CASE STATEMENT. This option in this specific scenario is better since you do not need to parse the content of the text.

If you need more flexiblke solution then you can use something like bellow (which will cost more since we parse the content of the text (split it and aggregate) but provide more flexible solution which might fit your needs

;With MyCTE AS (
    SELECT s.name, s.class, V.Num
    FROM StudentClass s
    CROSS APPLY string_split(s.class, ';') ca
    LEFT JOIN (VALUES (N'Math', '001'), (N'English', '002'),(N'Art', '003'),(N'Art History', '004')) V  (Class, Num) ON V.Class = ca.value
) 
SELECT [name], class, STRING_AGG(Num, ';')
FROM MyCTE
GROUP BY [name], class

Note! since I do not have table, I am using this part (VALUES (N'Math', '001'), (N'English', '002'),(N'Art', '003'),(N'Art History', '004')) V (Class, Num) in order to have the values in tabular structure on the fly

Answer 4

Please, please, PLEASE don't store multiple values in one columns. Add a one-to-many table of person to class. Then you can find all people in Art class without having to to a "contains" and worrying about Art History as well.

Then, use a primary key on each table that has no meaning outside of the table (e.g. don't use "name" as the class key. Then, you can link students to classes and have multiple columns for classes (name, code, etc.) that you can join to students however you want to.

In short, you're trying to solve a problem that you shouldn't have in the first place...