Loop through table and update a specific column

Question

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.

Regarding your second question in isolation: There is no need for a subselect when calculating the updated value. Your original statement could be written `UPDATE MyTable SET Category = UPPER(LEFT(Category,1)) + LOWER(SUBSTRING(Category,2,LEN(Category)))` to update all records in one statement. — T N, Feb 24 '22 at 22:58

Aaron Bertrand · Accepted Answer · 2022-02-24T22:45:23.123

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().

Updated db<>fiddle (thank you for the head start!)

Loop through table and update a specific column

1 Answers1