views:

1237

answers:

3

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
....
+2  A: 

maybe

UPDATE myTable 
SET description = newdesc
FROM Pieces
WHERE Pieces.id = myTable.itemid
Jhonny D. Cano -Leftware-
Glad for helping, can you accept my answer please?
Jhonny D. Cano -Leftware-
how to accept it? I cannot figure it out (no accept ui).
David.Chu.ca
Click on the Check below the upvote downvote arrows
Jhonny D. Cano -Leftware-
Thank you Jhonny, have a nice weekend!
David.Chu.ca
Glad it worked. Your anwer only partially addressed his issue - the update syntax, but not the actual alteration of the values.
Praesagus
You can use aliases for the updated table as well and make a normal join (i.e. UPDATE m SET ... FROM myTable m INNER JOIN Pieces p ON ...
Brimstedt
+2  A: 

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

Praesagus
yes. it should work as well. With is cool as well.
David.Chu.ca
A: 

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
GilM