Loop through table and update an specific column

February 25, 2022

I have the following table:

Id	Category
1	some thing
2	value

This table contains a lot of rows and what I’m trying to do is to update all the Category values to change every first letter to caps. For example, some thing should be Some Thing.

At the moment this is what I have:

UPDATE MyTable
    SET Category = (SELECT UPPER(LEFT(Category,1))+LOWER(SUBSTRING(Category,2,LEN(Category))) FROM MyTable WHERE Id = 1)
WHERE Id = 1;

But there are two problems, the first one is trying to change the Category Value to upper, because only works ok for 1 len words (hello=> Hello, hello world => Hello world) and the second one is that I’ll need to run this query X times following the Where Id = X logic. So my question is how can I update X rows? I was thinking in a cursor but I don’t have too much experience with it.

Here is a fiddle to play with.

>Solution :

You can split the words apart, apply the capitalization, then munge the words back together. No, you shouldn’t be worrying about subqueries and Id because you should always approach updating a set of rows as a set-based operation and not one row at a time.

;WITH cte AS
(
  SELECT Id, NewCat = STRING_AGG(CONCAT(
    UPPER(LEFT(value,1)),
    SUBSTRING(value,2,57)), ' ') 
    WITHIN GROUP (ORDER BY CHARINDEX(value, Category))
  FROM 
  (
    SELECT t.Id, t.Category, s.value 
    FROM dbo.MyTable AS t
    CROSS APPLY STRING_SPLIT(Category, ' ') AS s
  ) AS x GROUP BY Id
)
UPDATE t
  SET t.Category = cte.NewCat
  FROM dbo.MyTable AS t
  INNER JOIN cte ON t.Id = cte.Id;

This assumes your category doesn’t have non-consecutive duplicates within it; for example, bora frickin bora would get messed up (meanwhile bora bora fickin would be fine). It also assumes a case insensitive collation (which could be catered to if necessary).

In Azure SQL Database you can use the new enable_ordinal argument to STRING_SPLIT() but, for now, you’ll have to rely on hacks like CHARINDEX().