TSQL：使用With语句更新值？

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

我有一个表格,我希望更新其值。以下是TSQL代码的示例:

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?

此更新无效。通过注释SELECT,我可以看到结果是我需要的。我如何以批处理方式对一组行进行此更改?不确定是否可以通过WITH语句?

Here are some example data:

以下是一些示例数据:

....
xxxDESC_AyyyDESC_Bwwww
aaaDESC_AxxDESC_Beee
....

the udpated ones will be:

udpated将是:

....
xxxNEW_1yyyNEW_2wwww
aaaNEW_1xxNEW_2eee
....

3 个解决方案

#1

maybe

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

#2

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

#3

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:

顺便说一句,如果你真的想要使用CTE进行更新(虽然我更喜欢更直接的更新),你可以。但是,您必须在CTE中包含更新的列,并且您要更新的表是CTE名称,而不是原始表名。像这样:

;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

#1