TSQL: Update values by using With statement?

Question

I have a table with a column I would like to update its values. Here is an example of TSQL codes:

WITH Pieces(id, newdesc) AS
(
SELECT itemid, REPLACE(REPLACE(description, 'DESC_A', 'DESC_B'), 'NEW_1', 'NEW_2')
  FROM myTable
  WHERE description like '%DESC_A%DESC_B%'
)
-- SELECT * FROM Pieces
UPDATE myTable SET description = newdesc // not working, how?

This update is NOT working. By commenting out SELECT, I can see the result is what I need. How I can do this change in a batch way for a group of rows? Not sure is it possible by WITH statement?

Here are some example data:

....
xxxDESC_AyyyDESC_Bwwww
aaaDESC_AxxDESC_Beee
....

the udpated ones will be:

....
xxxNEW_1yyyNEW_2wwww
aaaNEW_1xxNEW_2eee
....

Solution 1

maybe

UPDATE myTable 
SET description = newdesc
FROM Pieces
WHERE Pieces.id = myTable.itemid

Solution 2

By the way, if you really want to use a CTE for the Update (although I prefer more straightforward updates), you can. But, you have to include the updated column in the CTE and the table you're updating is the CTE name, not the original tablename. Like this:

;WITH Pieces(id, description, newdesc) 
AS(
SELECT itemid, description, REPLACE(REPLACE(description, 'DESC_A', 'DESC_B'), 'NEW_1', 'NEW_2')  
FROM myTable  WHERE description like '%DESC_A%DESC_B%'
)
UPDATE Pieces SET description = newdesc 

Solution 3

This should do what you want. You don't need a with here.

UPDATE myTable SET description=REPLACE(REPLACE(description, 'DESC_A', 'NEW_1'), 'DESC_B', 'NEW_2')
WHERE description like '%DESC_A%' OR description like '%DESC_B%'

If both of these are never in the same fields you could use two separate statements for better resource mangement in case of a large table.

UPDATE myTable SET description=REPLACE(description, 'DESC_A', 'NEW_1')
WHERE description like '%DESC_A%'
go    
UPDATE myTable SET description=REPLACE(description, 'DESC_B', 'NEW_2')
WHERE description like '%DESC_B%'

HTH